[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!

Code:

---

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

---

Quote:

---

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

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.

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!