+ Reply to Thread
Results 1 to 6 of 6

Run-time error 1004; setting formula with string

  1. #1
    Registered User
    Join Date
    10-14-2013
    Location
    Saturn
    MS-Off Ver
    Excel 2010
    Posts
    28

    Run-time error 1004; setting formula with string

    So this code is supposed to find the column titled "Ingredient Cost Paid". For each cell in this column, this code is supposed to output the right two characters of the cell content in the adjacent cell. This is just a test function. I know I could have just used VBA RIGHT.
    The actual formula I'm trying to use is =IF(AND(CODE(RIGHT(A1))>74,CODE(RIGHT(A1))<>123),-1,1)*(10*LEFT(A1,LEN(A1)-1)+IF(CODE(RIGHT(A1))>120,0,CODE(RIGHT(A1))-IF(CODE(RIGHT(A1))>73,74,IF(CODE(RIGHT(A1))>64,64,48))))/100 where A1 is the targeted cell.

    Please Login or Register  to view this content.
    When I try to execute the sub, I get "Run-time error ' 1004: Application-defined or objected defined error" on line "Cells(i, colNum + 1).Formula = sFormula.
    Last edited by {=OR(value=array)}; 04-15-2014 at 04:54 PM.

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Run-time error 1004; setting formula with string

    I think what you need is to change:
    x = CStr(Cells(i, colNum).Value)
    to
    x = cells(i,colNum).address

  3. #3
    Registered User
    Join Date
    10-14-2013
    Location
    Saturn
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Run-time error 1004; setting formula with string

    Quote Originally Posted by ragulduy View Post
    I think what you need is to change:
    x = CStr(Cells(i, colNum).Value)
    to
    x = cells(i,colNum).address
    I want to store the cell contents as strings then use them in the long function mentioned above. The reason is that I want to replace each cells in the Ingredient Cost Paid column by the output of the custom function.

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Run-time error 1004; setting formula with string

    If I am understanding correctly, you might need to try:
    Please Login or Register  to view this content.
    It would be helpful if you could upload an example workbook with some idea of what the data that is being operated on is.

  5. #5
    Registered User
    Join Date
    10-14-2013
    Location
    Saturn
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Run-time error 1004; setting formula with string

    Quote Originally Posted by ragulduy View Post
    If I am understanding correctly, you might need to try:
    Please Login or Register  to view this content.
    It would be helpful if you could upload an example workbook with some idea of what the data that is being operated on is.
    You had a typo (should be
    Please Login or Register  to view this content.
    ) but otherwise it worked!
    Could you please explain what the does the extra "" do? What was wrong with my original?

    Also, I found out Asc is VBA equivalent of CODE(), so I ended writing the function as:
    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Run-time error 1004; setting formula with string

    Please Login or Register  to view this content.
    sformula will put
    =RIGHT(test,2)
    in a cell
    tformula will put
    =RIGHT("test",2)
    in a cell.

    sformula will try and find a named range called test and apply the right runction to the value of that range. If there is no such named range it will give an error.

    tformula will return "st"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Run Time Error 1004 pasting formula into worksheet using VBA.
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-09-2012, 09:42 PM
  2. Replies: 1
    Last Post: 09-23-2011, 10:35 AM
  3. Setting range to complex formula causes 1004 error
    By jsoderba in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-22-2010, 08:51 AM
  4. Runtime Error 1004 when Setting Setting Series Values
    By tonymctigue in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-09-2010, 10:05 AM
  5. Comparing a string to values in an array resulting in a Run time Error 1004
    By sgreni in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-15-2009, 04:21 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1