Results 1 to 5 of 5

Thread: [RESOLVED] Using Formula in VBA for Microsoft Excel. Unexpected results.

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jan 2013
    Posts
    25

    Resolved [RESOLVED] Using Formula in VBA for Microsoft Excel. Unexpected results.

    Hi all!

    Sorry, but I'm going to be breif since I don't have much time....

    I'm trying to create a formula cell that references only two others for simple multiplication.
    I've tried using FormulaR1C1, which should work, but it didn't.
    So instead, I used the "Formula" function (This is a much simpler line of code compared to the FormulaR1C1 line that I had)

    Code looks like this:
    "a" is a dimensioned Integer that represents the current row.
    Code:
     xlWS.Cells(a, 15).Formula = "=" & xlWS.Cells(a, 6).Address & "*D3"
    Which I would think should work... except it doesn't. Instead I get cells that are NOT multiplied by D3. Sometimes the resulting value is #REF! or #VALUE!
    In other words, the resulting cell value is pretty much unpredictable. :P

    If anybody know why the case may be, your help is greatly appreciated

    Thanks, and God Bless!
    Last edited by Millerni456; Jan 14th, 2013 at 07:57 AM.

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Using Formula in VBA for Microsoft Excel. Unexpected results.

    Code:
    xlWs.range("o" & a).value=xlWS.range("f" & a).value*xlWs.range("d3").value

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Using Formula in VBA for Microsoft Excel. Unexpected results.

    xlWS.Cells(a, 15).Formula = "=" & xlWS.Cells(a, 6).Address & "*D3"
    your formula works correctly for me (excel 2000) just pasted in
    assuming "F"(a) and "D3" have appropriate values and xlWS is the correct worksheet object
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Jan 2013
    Posts
    25

    Re: Using Formula in VBA for Microsoft Excel. Unexpected results.

    Well it's good to know that this code should work! :P

    FYI:
    I've gone ahead a re-checked to make sure the appropriate row is correct (the "a" value).
    Anyways, it prints the correct value, so it could not be that.

    I did find a very odd relationship between the cell addresses used however (which are "F"(a) and "D3").
    Between these two, the sum of the row values used (it varies for both cells) = a + 3 (which it should).
    So it appears to me that anything changed in one cell's row, an opposition but equal change happens to the other's row.

    Here are some examples:
    =$F$10*D2 Should be =$F$9*D3 10+2 = 9+3
    =$F$12*D1 Should be =$F$10*D3 12+1 = 10+3
    =$F$16*#REF! Should be =$F$13*D3 16+0 = 13+3 (Zero is an invalid index too!)
    =$F$18*D3 Should be =$F$18*D3 (this one is correct)
    =$F$39*D4 Should be =$F$40*D3 39+4 = 40+3

    Realizing that the "a" value didn't result in the correct address, for some reason, I changed my code to:
    Code:
    xlWS.Cells(a, 15).Formula = "=" & "F" & a & "*D3"
    This fixes the "F"(a) cell, but the D3 is still acting haywire.
    Last edited by Millerni456; Jan 14th, 2013 at 08:08 AM.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jan 2013
    Posts
    25

    Re: Using Formula in VBA for Microsoft Excel. Unexpected results.

    Okay! It's been fixed!

    Unfortunately I cannot provide any reasons to the fix, but what I did was made this my last modifications to the Excel Spreadsheet. Even though I don't think something may have been changing these values mid-process, it's possible I coded something that gave bad results.

    Anyways, the code mentioned now works.

    Thanks all!

Posting Permissions

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



Click Here to Expand Forum to Full Width