Avatar of looper8
looper8

asked on 

Getting Excel cell's formula programatically

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?
Microsoft AccessMicrosoft Excel

Avatar of undefined
Last Comment
looper8
Avatar of Vadim Rapp
Vadim Rapp
Flag of United States of America image

If you want the value from (-1)th row and column 2, it will be =R[-1]C2
ASKER CERTIFIED SOLUTION
Avatar of Vadim Rapp
Vadim Rapp
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of looper8
looper8

ASKER

Thanks vadim.  You're quite right, and that's so simple I can't believe I didn't see it (actually I can, I'm seriously tired!).
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo