R1C1 formula is making me crazy!

[zookeepertx]

Hello again, all.

I'm trying to get an R1C1 formula to act right for me in a macro that I'm working on and am about to tear my hair out! I know it's just a syntax error somewhere (or else I'm just not holding my mouth right) but can't find it. I've copied the syntax exactly from another macro, obviously changing the references, where the formula works perfectly.

I want column O to look at P and if P is blank, then don't put anything in O. If there is something in P, then I need a formula in O. (I'll show it below)
I also want column R to look at P and if P is blank then don't put anything in R. If there is something in P, then I need another formula in R, which I'll also show below.

It shouldn't be this hard and I KNOW one of you will spot it right away, but I've been looking at it too long and am now so mad at it, I'm never going to find it!

Anyway, here's the (apparently incorrect) code/formula I have now:

lr = Range("A" & Rows.Count).End(xlUp).Row

With Range("O5:O" & lr)
    .FormulaR1C1 = "=IF(ISBLANK(RC[+1]),"",ROUNDUP(RC[+1],0)*2.2046)"
With Range("R5:R" & lr)
    .FormulaR1C1 = "=IF(ISBLANK(RC[-2]),"",RC[-3] - RC[-6])"

I will greatly appreciate any suggestions!

Jenny

 

[Joe4]

There is a little secret to doing these - let Excel figure out the formula for you!

If you turn on your Macro Recorder, and record yourself manually entering the formula you want in cell O5, and then stop the Macro Recorder, you will have recorded the exact formula you need, and you can copy/paste the formula part of your code into what you have now.

Try it and see!

 

[zookeepertx]

Hi Joe,
YAY!!! That worked perfectly! It was the quotation marks; it wanted 4 of them instead of just 2. I'm so happy now; THANK YOU!

Jenny

 

[Joe4]

You are welcome!

Yes, the double-quotes use as text qualifiers versus literal double-quotes can get quite messy at times.
I like to let Excel figure it out for me!

By the way, love the "CDO" signature!

 

[zookeepertx]

You are welcome!

Yes, the double-quotes use as text qualifiers versus literal double-quotes can get quite messy at times.
I like to let Excel figure it out for me!

By the way, love the "CDO" signature!

Yeah, that quotation thing would never have occurred to me if I was left to my own devices, LOL!

The first time I saw the CDO thing, my first thought was "Hey! Somebody's been watching me!"