dynamic formula

[geebee]

hi,

i have the following:

Range("AJ8:AJ" & lastrecordNew2).Select
Selection.Formula = "=IF(ISERROR(BB8/BC8),'No Data',BB8/BC8)"

but i have a problem with this because its not working right. i think it
has something to do with the syntax or something around the No Data.

how can i fix this?

thanks in advance,
geebee

 

[Rick Rothstein \(MVP - VB\)]

Apostrophes do not delimit text in Excel; quote marks do. However, quote
marks inside of a String constant must be doubled up in order for VB to
treat them as real quote marks and not text String delimiters. Try it this
way...

Range("AJ8:AJ" & lastrecordNew2).Formula = _
"=IF(ISERROR(BB8/BC8),""No Data"",BB8/BC8)"

Also notice that there is no reason to select the cell in order to address
one of its properties.

Rick

 

[Dave Peterson]

Dim LastRecordNew2 As Long
LastRecordNew2 = 33
With ActiveSheet
.Range("AJ8:AJ" & LastRecordNew2).Formula _
= "=IF(ISERROR(BB8/BC8),""No Data"",BB8/BC8)"
End With

You have to use " for strings (not apostrophes) in your formulas. And in VBA,
you have to double them up to get one.