I'm creating a report from an Access adp which is output to Excel. Some calcuations need doing on the data once the sheet is populated. One of these is currently done by:
For x = 1 To rst.Fields.Count - 3
oSheet.Cells(intLoopCounter + 1, x + 3).Value = "=(R[-1]C/" & oSheet.Cells(intLoopCounter, 2).Value & ")"
Next
The value that is inserted into each of these cells is something like =(D11/82) or =(E11/82) etc etc
What I need is to have =(D11/B11) or =(E11/B11) etc (so B11 in this case is 82, it will always be in column B but I don't know in advance which row - hence intLoopCounter)
I thought maybe I could use oSheet.Cells(intLoopCounter + 1, x + 3).Value = "=(R[-1]C/" & oSheet.Cells(intLoopCounter, 2).Formula & ")" but that gives error 1004 (Application defined or object defined error).
If I do oSheet.Cells(intLoopCounter + 1, x + 3).Value = "=(R[-1]C/R[-1]C[-2]") it inserts a formula correctly, but of course takes it from a relative position rather than a static one. So I tried $R[-1]$C[-2] but that creates the 1004 again.
Any ideas?