I am writing a macro of which one of the statement have the following
formula

ActiveCell.FormulaR1C1 = "=IF(BK2=""PC
Number"",VLOOKUP(BN2,'O:\Documentation\Asset
Management\Hardware\[Assets -
Latest.xls]Sheet1'!$F$2:$CE$350,74,FALSE),IF(BK2=""Login
ID"",VLOOKUP(BN2,'O:\Documentation\Asset Management\User\[All
Users.xls]Sheet1'!$F$2:$Z$550,21,FALSE),""**CHECK""))"

The above was recorded using macro record and was working find. But
when trying to rerun the macro if produced an error on the syntax.

It seems the confusion here is the number of quotes ". Please advise
how best to resolve the assignment problem.


---
Message posted from http://www.ExcelForum.com/

This works for me

Dim sPath As String
sPath = "O:\Documentation\Asset Management\Hardware\"
ActiveCell.Formula = "=IF(BK2=""PC Number"",VLOOKUP(BN2,'" & _
sPath & "Hardware\" & _
"[Assets -
Latest.xls]Sheet1'!$F$2:$CE$350,74,FALSE)," & _
"IF(BK2=""Login ID"",VLOOKUP(BN2,'" & _
sPath & "User\[" & _
"All
Users.xls]Sheet1'!$F$2:$Z$550,21,FALSE),""**CHECK""))"

Note Formula, not FormulaR1C1.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"pgi " wrote in message
...
I am writing a macro of which one of the statement have the following
formula

ActiveCell.FormulaR1C1 = "=IF(BK2=""PC
Number"",VLOOKUP(BN2,'O:\Documentation\Asset
Management\Hardware\[Assets -
Latest.xls]Sheet1'!$F$2:$CE$350,74,FALSE),IF(BK2=""Login
ID"",VLOOKUP(BN2,'O:\Documentation\Asset Management\User\[All
Users.xls]Sheet1'!$F$2:$Z$550,21,FALSE),""**CHECK""))"

The above was recorded using macro record and was working find. But
when trying to rerun the macro if produced an error on the syntax.

It seems the confusion here is the number of quotes ". Please advise
how best to resolve the assignment problem.


---
Message posted from http://www.ExcelForum.com/