Help w/ VBA formula, variables & errors

[boznian]

I am stuck again. I need to be able to set a formula with VB that contains variables for the row number. Every effort comes up with "Expected End of Statement" errors. Here is (one of ) the formula's that work fine if placed directly via Excel:

 =IF(OR(T323="",V323<>""),"",IF(MOD(TODAY()-T323,7)=0,"Renew",""))

I have been attempting things along these lines:

     Cells(crow, "U:U").Formula = "=" "IF(OR(("T:T", crow)="",("V:V", crow)<>""),"",IF(MOD(TODAY()-("T:T", crow),7)=0,"Renew",""))"

No matter how I slice and dice, can't get past multiple errors. btw, I am calling

     crow = ActiveCell.Row

earlier in the script and it works fine in all other parts of the script.

Another formula giving me the same fits:

 =IF(T322,IF(AND(T322>0,W322<>""),"",(TODAY()-T322)),"")

what am I doing wrong?

 

[repairman615]

Hello,

I am thinking this has to do with the quotes.

Try double quotes like:

"=IF(OR(T323="""",V323<>""""),"""",IF(MOD(TODAY()-T323,7)=0,""Renew"",""""))"

 

[boznian]

Thanks for the reply, but you are working on the wrong formula. That one is the formula that works fine directly in Excel. I need to be able to place it via VB with variable row information. My attempts are similar to the second formula that I posted. I do believe you are correct that it is an issue with the quotes, but I don't know how to get around it.

 

[sous2817]

Not test but like this perhaps?:

"=IF(or(T" & crow & "="""",V" & crow & "<>""""),"""",IF(MOD(Today()-T" & crow & ",7)=0,""Renew"",""""))"

 

[gregtx81]

To place a formula into a range(cell) in VBA use the .FormulaLocal or .FormulaR1C1 properties. It sounds like you want dynamic references so FormulaR1C1 will be more useful.

Easiest way to see how this works is to use the macro recorder and enter a formula manually, then go view the code.

 

[mikerickson]

The second snip of code in the OP is lacking any concatenation operators (&). Putting them in should solve your problem.

 

[boznian]

The second snip of code in the OP is lacking any concatenation operators (&). Putting them in should solve your problem.

Somehow I mistakenly took these out when writing the post - they were there in my original code and I still had the errors.

"Easiest way to see how this works is to use the macro recorder and enter a formula manually, then go view the code."

As silly a it sounds, I was completely unaware that this could be done and work in a "relative" fashion! The last time I tried this it worked one time, but never on the next row?? Maybe an old, old version of Excel? Anyway, got it working and opened many new possibilities, thanks