Compile Error: Syntax Error

[XLerator]

I was hoping someone on this board can help me out...

This is what I want it to do:

SUB
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "TYPE"
Range("C2").Select
ActiveCell.FormulaR1C1 = "IF(L2="Y","Cruise",IF(R2="winter",VLOOKUP(B2,Winter!$A$1:$C$200,2,0),IF(A2="FCO",VLOOKUP(A2,Lookup!$A$1:$B$200,2,0),IF(A2="VCE",VLOOKUP(A2,Lookup!$A$1:$B$200,2,0),IF(A2="SUF",VLOOKUP(A2,Lookup!$A$1:$B$200,2,0),IF(A2="PSR",VLOOKUP(A2,Lookup!$A$1:$B$200,2,0),VLOOKUP(B2,Lookup!$A$1:$B$200,2,0)))))))"
Range("C3").Select
LastRow = Range("G1:G" & Range("G1").End(xlDown).Row).Rows.Count
Range("C2" & ":C" & LastRow).FillDown
END SUB

When I type in the BIG formula, it highlights the letter Y (in IF(L2="Y"...)) in brackets and says syntax error---what's going on?

 

[Jay Petrulis]

Hi,

When writing formulas to the worksheet from VBA, you must enclose all text entries with double double quotes. Also, you formula will fail because you don't enter an equal sign.

Note also that you are inserting a row at col c, and then testing for the number of rows used in col g. Prior to the start of the routine, that col g data was in col F, but the code somewhat "hides" it. Be careful here.


Try the following,
------------------
Sub Test()
Dim lastrow As Long
Columns("C:C").Insert Shift:=xlToRight
Range("C1") = "TYPE"
lastrow = Range("G1:G" & Range("G1").End(xlDown).Row).Rows.Count
Range("C2:C" & lastrow) = "=IF(L2=""Y"",""Cruise"",IF(R2=""winter"",VLOOKUP(B2,Winter!$A$1:$C$200,2,0),IF(A2=""FCO"",VLOOKUP(A2,Lookup!$A$1:$B$200,2,0),IF(A2=""VCE"",VLOOKUP(A2,Lookup!$A$1:$B$200,2,0),IF(A2=""SUF"",VLOOKUP(A2,Lookup!$A$1:$B$200,2,0),IF(A2=""PSR"",VLOOKUP(A2,Lookup!$A$1:$B$200,2,0),VLOOKUP(B2,Lookup!$A$1:$B$200,2,0)))))))"
End Sub
-------------------

Bye,
Jay