A formula that evaluates the value of a cell relatively addressed.

[NorMacro]

Never posted before...
If you please....
I have the following working formula in cell H69 (for example):

=IF(D69=0,"","Limit")
When D69 equals zero, H69 is blank, otherwise it shows the string
"Limit".

A macro will assign this formula to a particular cell and it won't
always be row 69. How can I replace the D69 with something akin to
"current column minus 4" & "current row"?

The VBA is
ActiveCell = <the formula above>

Thanks for any ideas!

 

[Peter T]

Sub test2()
Dim sFmla As String

sFmla = "=IF(RC[-4]=0,"""",""Limit"")"

ActiveCell = sFmla
' or
ActiveCell.Formula = sFmla
' or
ActiveCell.FormulaR1C1 = sFmla

'no need to use activecell, eg
Range("E1:E5").Formula = sFmla

End Sub

Regards,
Peter T

 

[JE McGimpsey]

One way:

ActiveCell.FormulaR1C1 = "=IF(R[]C[-4]=0,"""",""Limit"")"

 

[NorMacro]

Thank you. Your adjustment to my formula along with your method(s)
worked smoothly.

I want to point out though that, as you went about it, the macro
evaluates the IF statement and assigns either blank or "Limit" to the
cell. I would sure like to have a formula in the target cell, such
that it can continue to evaluate the conditions in the cell four
columns left and continue to display either blank or "Limit".

If you have the patience with me...thanks in advance!

 

[Guest]

Did you look in the formula bar for the cells you ran the macro on?
It looks to me like the macro put the formula in the cell and it will
continue to evaluate and execute the result.

 

[NorMacro]

Peter, JE, JL,

I just found that I had not in fact removed an old line of macro code
that I believed I had removed. It now works perfectly! Thanks for
your time!