Access VBA formula in Excel (A2003 SP 2)

Topic

New Reply

I know I have this asked and answered before, but I can’t for the life of me find it.
I am using the following VBA code from an Access module to put a formula in a specific cell
strSQL = “P2:P” & r
.Range(strSQL).FormulaR1C1 = “=IF(SUM(J2-H2-M2)<U2,(J2-H2-M2),U2)"

When I open the spreadsheet instead of the above, I have "=IF(SUM('J2'-'H2'-'M2')<'U2',('J2'-'H2'-'M2'),'U2')". For some reason I am getting apostrophe's (') after each cell. What am I doing wrong?

Thanks in advance for your guidance.

Reply | Quote

Replies

FormulaR1C1 expects a formula using R1C1 style cell refererences, not A1 style cell references, so your code is inconsistent. Try

.Range(strSQL).Formula = “=IF(SUM(J2-H2-M2)<U2,(J2-H2-M2),U2)"

By the way, it can be simplified to

.Range(strSQL).Formula = "=MIN(J2-H2-M2,U2)"

Note: strSQL is a confusing name for a variable that holds a range reference.

Reply | Quote

Hans,

You are right about the strSQL, but I needed a variable and that one was already diminsioned, so I was lazy and used it. I need to be more methodical on how I use things. I’ll give it a try, and I have no doubt it will work. THANKS!

As a side note, do you know if there was a reason that it automatically placed the apostrophe’s in the cell? Or is it just one of those anomalies?

THANKS!

Ken

Reply | Quote

The cause of the apostrophes was that the formula was not valid as an R1C1 style formula, so Excel tried to interpret the cell references as names.

Reply | Quote

THANK YOU. It’s hard to outsmart these things when they are so logical all the time.

Reply | Quote