.Formula Help

I know this formula works when I enter it right in the excel sheet, but I'm trying to enter this formula to the sheet via vba. I used the .Formula command before but this has quotes and different character that are probablly conflicting with vb to cause the error.

Could you tell me what characters are bad here, and what to do about them?


ActiveSheet.Range("B" & NextRow).Formula = "=IF(C" & NextRow & "<>"", VLOOKUP(C" & NextRow & ",[EstProductList.xls]Main!$B$2:$AH$1500,2,FALSE), "")"


Thanks.

I assume you want your If formula to be something like:
=if(C1<>"",value,""). In this case, you need to use 4 quotes to get a double quote in a string, not two, like so:
ActiveSheet.Range("B" & nextrow).Formula = _
"=IF(C" & nextrow & "<>"""", VLOOKUP(C" & nextrow & _
",[EstProductList.xls]Main!$B$2:$AH$1500,2,FALSE), """" )"

Or, you could concatenate a double-quote using the Chr$(34) function.

ActiveSheet.Range("B" & nextrow).Formula = _
"=IF(C" & nextrow & "<>" & Chr$(34) & Chr$(34) & ", VLOOKUP(C" & nextrow & _
",[EstProductList.xls]Main!$B$2:$AH$1500,2,FALSE)," & Chr$(34) & Chr$(34) & ")"

Nate