move formula to VBA function...

[RompStar]

Trying to move this formula from the sheet formula to a VBA function,
because using the sheet formula, the formula is lost after being used
once, incase the user changes their mind and they go back...

=IF(E4="N/A", 0, "")

Bascially formula lies in cells F4-F1000

but the formula looks into E4, If there is a Value there N/A chosen
from a drop-down list, then pre-fill 0 into F4

The formulas are copies to F1000 Range and the Range is static, it
never changes...

for i = 4 to 1000
Cells(i,6).formula = _
"=IF((INDIRECT(ADDRESS(ROW(),COLUMN()-1))="N/A"),0, "")"
next i

Currently I get an error using this:

Compile Error:

Expected: end of statement

any ideas what is wrong ?

 

[Trevor Shuttleworth]

Try:

Sub test()
Range("F4:F1000").Formula = "=IF(E4=""N/A"", 0, """")"
End Sub

Regards

Trevor

 

[RompStar]

cool, I'll fiddle around with it, thanks.