If statement giving syntax error when run macro

A

aileen

I am copying some if statements with a macro into a workbook and when I have
the following code in the macro it works perfectly:

Range("H1").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-5]=""JAN"",""JAN"",IF(RC[-5]=""FEB"",""FEB"",IF(RC[-5]=""MAR"",""MAR"",IF(RC[-5]=""APR"",""APR"",IF(RC[-5]=""MAY"",""MAY"",IF(RC[-5]=""JUN"",""JUN"",IF(RC[-5]=""JUL"",""JUL"",IF(RC[-5]=""AUG"",""AUG"",IF(RC[-5]=""SEP"",""SEP"",IF(RC[-5]=""OCT"",""OCT"",IF(RC[-5]=""NOV"",""NOV"",IF(RC[-5]=""DEC"",""DEC""))))))))))))"
Range("I1").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-5]=8,2008,IF(RC[-5]=9,2009,IF(RC[-5]=10,2010,IF(RC[-5]=11,2011,IF(RC[-5]=12,2012,IF(RC[-5]=13,2013,IF(RC[-5]=14,2014,IF(RC[-5]=15,2015,IF(RC[-5]=16,2016,IF(RC[-5]=17,2017,IF(RC[-5]=18,2018,IF(RC[-5]=19,2019,IF(RC[-5]=20,2020)))))))))))))"
Range("K1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]=""C"",RC[-10],0)"
Range("L1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-6]=""P"",RC[-11],0)"
Range("L2").Select

But as soon as I add this IF statement I get a syntax error:

Range("G1").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-5]=""SPX"",""SPX"",IF(RC[-5]=""SXB"",""SPX"",IF(RC[-5]=""SPQ"",""SPX"",IF(RC[-5]=""SPT"",""SPX"",IF(RC[-5]=""SZP"",""SPX"",IF(RC[-5]=""SXY"",""SPX"",IF(RC[-5]=""SXZ"",""SPX"",IF(RC[-5]=""SXM"",""SPX"",IF(RC[-5]=""SPB"",""SPX"",IF(RC[-5]=""SVP"",""SPX"",IF(RC[-5]=""SZJ"",""SPX"",IF(RC[-5]=""SZV"",""SPX"",IF(RC[-5]=""SPL"",""SPX"",IF(RC[-5]=""SYZ"",""SPX"",IF(RC[-5]=""SXG"",""SPX"",IF(RC[-5]=""SZT"",""SPX"",IF(RC[-5]=""QSE"",""QSPX"",IF(RC[-5]=""SAQ"",""QSPX"",IF(RC[-5]=""SLQ"",""QSPX"",IF(RC[-5]=""SWV"",""SPY"",IF(RC[-5]=""SZC"",""SPY"",IF(RC[-5]=""SWG"",""SPY"",IF(RC[-5]=""SFB"",""SPY"",IF(RC[-5]=""SYH"",""SPY"",IF(RC[-5]=""SUE"",""SPY"",IF(RC[-5]=""FYS"",""SPY"",IF(RC[-5]=""FYN"",""SPY"",IF(RC[-5]=""YQA"",""SPY"",IF(RC[-5]=""YAZ"",""SPY"",IF(RC[-5]=""CYU"",""SPY"",IF(RC[-5]=""JCA"",""SPY"",IF(RC[-5]=""CYY"",""SPY"",IF(RC[-5]=""RDQ"",""QSPY"",IF(RC[-5]=""RQQ"",""QSPY"",IF(RC[-5]=""SPY"",""SPY"",IF(RC[-5]=""SZQ"",""QSPX"",IF(RC[-5]=""JXD"",""JXA"",IF(RC[-5]=""JXE"",""JXA"",IF(RC[-5]=""JXA"","
"JXA"",IF(RC[-5]=""JXB"",""JXA"",IF(RC[-5]=""SPZ"",""SPX"",IF(RC[-5]=""SKQ"",""QSPX"",IF(RC[-5]=""SQP"",""QSPX"",
IF(RC[-2]=""AM"",""SPC"",IF(RC[-2]=""S&P"",""SP"",IF(RC[-2]=""EMINI"",""ES"",IF(RC[-2]=""IMM"",""EV"",IF(RC[-2]=""0810"",""SPC"",IF(RC[-2]=""0811"",""SPC"",IF(RC[-2]=""0812"",""SPC"",IF(RC[-2]=""0901"",""SPC"",IF(RC[-2]=""0902"",""SPC"",IF(RC[-2]=""0903"",""SPC"",IF(RC[-2]=""0904"",""SPC"",IF(RC[-2]=""0905"",""SPC"")))))))))))))))))))))))))))))))))))))))))))))))))))))))"
Range("G2").Select
Range("G1:I1").Select
Selection.Copy
Range("G2:I400").Select
ActiveSheet.Paste

Please let me know what is wrong with the IF statment. This IF statement
works perfectly when I just copy it into a cell. It only doesn't work when I
run it from a macro. Thanks!
 
J

Jim Thomlinson

A string is limited in the number of characters you can have. Try splitting
the string into 2...

Range("G1").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-5]=""SPX"",""SPX"",IF(RC[-5]=""SXB"",""SPX"",IF(RC[-5]=""SPQ"",""SPX"",IF(RC[-5]=""SPT"",""SPX"",IF(RC[-5]=""SZP"",""SPX"",IF(RC[-5]=""SXY"",""SPX"",IF(RC[-5]=""SXZ"",""SPX"",IF(RC[-5]=""SXM"",""SPX"",IF(RC[-5]=""SPB"",""SPX"",IF(RC[-5]=""SVP"",""SPX"",IF(RC[-5]=""SZJ"",""SPX"",IF(RC[-5]=""SZV"",""SPX"",IF(RC[-5]=""SPL"",""SPX"",IF(RC[-5]=""SYZ"",""SPX"",IF(RC[-5]=""SXG"",""SPX"",IF(RC[-5]=""SZT"",""SPX"",IF(RC[-5]=""QSE"",""QSPX"",IF(RC[-5]=""SAQ"",""QSPX"",IF(RC[-5]=""SLQ"",""QSPX"",IF(RC[-5]=""SWV"",""SPY"",IF(RC[-5]=""SZC"",""SPY"",IF(RC[-5]=""SWG"",""SPY"",IF(RC[-5]=""SFB"",""SPY"",IF(RC[-5]=""SYH"",""SPY"",IF(RC[-5]=""SUE"",""SPY"",IF(RC[-5]=""FYS"",""SPY"",IF(RC[-5]=""FYN"",""SPY"",IF(RC[-5]=""YQA"",""SPY"",IF(RC[-5]=""YAZ"",""SPY"",IF(RC[-5]=""CYU"",""SPY"",IF(RC[-5]=""JCA"",""SPY"",IF(RC[-5]=""CYY"",""SPY"",IF(RC[-5]=""RDQ"",""QSPY"",IF(RC[-5]=""RQQ"",""QSPY"",IF(RC[-5]=""SPY"",""SPY"",IF(RC[-5]" &
"=""SZQ"",""QSPX"",IF(RC[-5]=""JXD"",""JXA"",IF(RC[-5]=""JXE"",""JXA"",IF(RC[-5]=""JXA"",
"JXA"",IF(RC[-5]=""JXB"",""JXA"",IF(RC[-5]=""SPZ"",""SPX"",IF(RC[-5]=""SKQ"",""QSPX"",IF(RC[-5]=""SQP"",""QSPX"",
IF(RC[-2]=""AM"",""SPC"",IF(RC[-2]=""S&P"",""SP"",IF(RC[-2]=""EMINI"",""ES"",IF(RC[-2]=""IMM"",""EV"",IF(RC[-2]=""0810"",""SPC"",IF(RC[-2]=""0811"",""SPC"",IF(RC[-2]=""0812"",""SPC"",IF(RC[-2]=""0901"",""SPC"",IF(RC[-2]=""0902"",""SPC"",IF(RC[-2]=""0903"",""SPC"",IF(RC[-2]=""0904"",""SPC"",IF(RC[-2]=""0905"",""SPC"")))))))))))))))))))))))))))))))))))))))))))))))))))))))"
--
HTH...

Jim Thomlinson


aileen said:
I am copying some if statements with a macro into a workbook and when I have
the following code in the macro it works perfectly:

Range("H1").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-5]=""JAN"",""JAN"",IF(RC[-5]=""FEB"",""FEB"",IF(RC[-5]=""MAR"",""MAR"",IF(RC[-5]=""APR"",""APR"",IF(RC[-5]=""MAY"",""MAY"",IF(RC[-5]=""JUN"",""JUN"",IF(RC[-5]=""JUL"",""JUL"",IF(RC[-5]=""AUG"",""AUG"",IF(RC[-5]=""SEP"",""SEP"",IF(RC[-5]=""OCT"",""OCT"",IF(RC[-5]=""NOV"",""NOV"",IF(RC[-5]=""DEC"",""DEC""))))))))))))"
Range("I1").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-5]=8,2008,IF(RC[-5]=9,2009,IF(RC[-5]=10,2010,IF(RC[-5]=11,2011,IF(RC[-5]=12,2012,IF(RC[-5]=13,2013,IF(RC[-5]=14,2014,IF(RC[-5]=15,2015,IF(RC[-5]=16,2016,IF(RC[-5]=17,2017,IF(RC[-5]=18,2018,IF(RC[-5]=19,2019,IF(RC[-5]=20,2020)))))))))))))"
Range("K1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]=""C"",RC[-10],0)"
Range("L1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-6]=""P"",RC[-11],0)"
Range("L2").Select

But as soon as I add this IF statement I get a syntax error:

Range("G1").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-5]=""SPX"",""SPX"",IF(RC[-5]=""SXB"",""SPX"",IF(RC[-5]=""SPQ"",""SPX"",IF(RC[-5]=""SPT"",""SPX"",IF(RC[-5]=""SZP"",""SPX"",IF(RC[-5]=""SXY"",""SPX"",IF(RC[-5]=""SXZ"",""SPX"",IF(RC[-5]=""SXM"",""SPX"",IF(RC[-5]=""SPB"",""SPX"",IF(RC[-5]=""SVP"",""SPX"",IF(RC[-5]=""SZJ"",""SPX"",IF(RC[-5]=""SZV"",""SPX"",IF(RC[-5]=""SPL"",""SPX"",IF(RC[-5]=""SYZ"",""SPX"",IF(RC[-5]=""SXG"",""SPX"",IF(RC[-5]=""SZT"",""SPX"",IF(RC[-5]=""QSE"",""QSPX"",IF(RC[-5]=""SAQ"",""QSPX"",IF(RC[-5]=""SLQ"",""QSPX"",IF(RC[-5]=""SWV"",""SPY"",IF(RC[-5]=""SZC"",""SPY"",IF(RC[-5]=""SWG"",""SPY"",IF(RC[-5]=""SFB"",""SPY"",IF(RC[-5]=""SYH"",""SPY"",IF(RC[-5]=""SUE"",""SPY"",IF(RC[-5]=""FYS"",""SPY"",IF(RC[-5]=""FYN"",""SPY"",IF(RC[-5]=""YQA"",""SPY"",IF(RC[-5]=""YAZ"",""SPY"",IF(RC[-5]=""CYU"",""SPY"",IF(RC[-5]=""JCA"",""SPY"",IF(RC[-5]=""CYY"",""SPY"",IF(RC[-5]=""RDQ"",""QSPY"",IF(RC[-5]=""RQQ"",""QSPY"",IF(RC[-5]=""SPY"",""SPY"",IF(RC[-5]=""SZQ"",""QSPX"",IF(RC[-5]=""JXD"",""JXA"",IF(RC[-5]=""JXE"",""JXA"",IF(RC[-5]=""JXA"","
"JXA"",IF(RC[-5]=""JXB"",""JXA"",IF(RC[-5]=""SPZ"",""SPX"",IF(RC[-5]=""SKQ"",""QSPX"",IF(RC[-5]=""SQP"",""QSPX"",
IF(RC[-2]=""AM"",""SPC"",IF(RC[-2]=""S&P"",""SP"",IF(RC[-2]=""EMINI"",""ES"",IF(RC[-2]=""IMM"",""EV"",IF(RC[-2]=""0810"",""SPC"",IF(RC[-2]=""0811"",""SPC"",IF(RC[-2]=""0812"",""SPC"",IF(RC[-2]=""0901"",""SPC"",IF(RC[-2]=""0902"",""SPC"",IF(RC[-2]=""0903"",""SPC"",IF(RC[-2]=""0904"",""SPC"",IF(RC[-2]=""0905"",""SPC"")))))))))))))))))))))))))))))))))))))))))))))))))))))))"
Range("G2").Select
Range("G1:I1").Select
Selection.Copy
Range("G2:I400").Select
ActiveSheet.Paste

Please let me know what is wrong with the IF statment. This IF statement
works perfectly when I just copy it into a cell. It only doesn't work when I
run it from a macro. Thanks!
Click to expand...
 
M

Matt S

wow that is one helluva IF statement! I think you'd probably have length
issues running it as a macro. How can you possibly keep that straight!?

What about this instead?

Sub hugeifstatement()

Dim Number As String
Number = ActiveCell.Offset(0, -5).Value

Select Case Number
Case "SPX", "SXB", "SPQ", "SPT", "SZP", "SXY", "SXZ", "SXM", "SPB",
"SVP", "SZJ", "SZV", "SPL", "SYZ", "SXG", "SZT"
ActiveCell.Value = "SPX"
Case "QSE", "SAQ", "SLQ", "SZQ"
ActiveCell.Value = "QSPX"
Case "SWV", "SZC", "SWG", "SFB", "SYH", "SUE", "FYS", "FYN", "YQA",
"YAZ", "CYU", "JCA", "CYY", "SPY"
ActiveCell.Value = "SPY"
Case "RDQ", "RQQ"
ActiveCell.Value = "QSPY"
Case "JXD", "JXE", "JXA", "JXB"
ActiveCell.Value = "JXA"
Case "SPZ"
ActiveCell.Value = "SPX"
Case "SKQ", "SQP"
ActiveCell.Value = "QSPX"
Case "AM", "0810", "0811", "0812", "0901", "0902", "0903", "0904", "0905"
ActiveCell.Value = "SPC"
Case "S&P"
ActiveCell.Value = "SP"
Case "EMINI"
ActiveCell.Value = "ES"
Case "IMM"
ActiveCell.Value = "EV"
Case Else
MsgBox Prompt:="Not defined"


End Select

End Sub




aileen said:
I am copying some if statements with a macro into a workbook and when I have
the following code in the macro it works perfectly:

Range("H1").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-5]=""JAN"",""JAN"",IF(RC[-5]=""FEB"",""FEB"",IF(RC[-5]=""MAR"",""MAR"",IF(RC[-5]=""APR"",""APR"",IF(RC[-5]=""MAY"",""MAY"",IF(RC[-5]=""JUN"",""JUN"",IF(RC[-5]=""JUL"",""JUL"",IF(RC[-5]=""AUG"",""AUG"",IF(RC[-5]=""SEP"",""SEP"",IF(RC[-5]=""OCT"",""OCT"",IF(RC[-5]=""NOV"",""NOV"",IF(RC[-5]=""DEC"",""DEC""))))))))))))"
Range("I1").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-5]=8,2008,IF(RC[-5]=9,2009,IF(RC[-5]=10,2010,IF(RC[-5]=11,2011,IF(RC[-5]=12,2012,IF(RC[-5]=13,2013,IF(RC[-5]=14,2014,IF(RC[-5]=15,2015,IF(RC[-5]=16,2016,IF(RC[-5]=17,2017,IF(RC[-5]=18,2018,IF(RC[-5]=19,2019,IF(RC[-5]=20,2020)))))))))))))"
Range("K1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]=""C"",RC[-10],0)"
Range("L1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-6]=""P"",RC[-11],0)"
Range("L2").Select

But as soon as I add this IF statement I get a syntax error:

Range("G1").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-5]=""SPX"",""SPX"",IF(RC[-5]=""SXB"",""SPX"",IF(RC[-5]=""SPQ"",""SPX"",IF(RC[-5]=""SPT"",""SPX"",IF(RC[-5]=""SZP"",""SPX"",IF(RC[-5]=""SXY"",""SPX"",IF(RC[-5]=""SXZ"",""SPX"",IF(RC[-5]=""SXM"",""SPX"",IF(RC[-5]=""SPB"",""SPX"",IF(RC[-5]=""SVP"",""SPX"",IF(RC[-5]=""SZJ"",""SPX"",IF(RC[-5]=""SZV"",""SPX"",IF(RC[-5]=""SPL"",""SPX"",IF(RC[-5]=""SYZ"",""SPX"",IF(RC[-5]=""SXG"",""SPX"",IF(RC[-5]=""SZT"",""SPX"",IF(RC[-5]=""QSE"",""QSPX"",IF(RC[-5]=""SAQ"",""QSPX"",IF(RC[-5]=""SLQ"",""QSPX"",IF(RC[-5]=""SWV"",""SPY"",IF(RC[-5]=""SZC"",""SPY"",IF(RC[-5]=""SWG"",""SPY"",IF(RC[-5]=""SFB"",""SPY"",IF(RC[-5]=""SYH"",""SPY"",IF(RC[-5]=""SUE"",""SPY"",IF(RC[-5]=""FYS"",""SPY"",IF(RC[-5]=""FYN"",""SPY"",IF(RC[-5]=""YQA"",""SPY"",IF(RC[-5]=""YAZ"",""SPY"",IF(RC[-5]=""CYU"",""SPY"",IF(RC[-5]=""JCA"",""SPY"",IF(RC[-5]=""CYY"",""SPY"",IF(RC[-5]=""RDQ"",""QSPY"",IF(RC[-5]=""RQQ"",""QSPY"",IF(RC[-5]=""SPY"",""SPY"",IF(RC[-5]=""SZQ"",""QSPX"",IF(RC[-5]=""JXD"",""JXA"",IF(RC[-5]=""JXE"",""JXA"",IF(RC[-5]=""JXA"","
"JXA"",IF(RC[-5]=""JXB"",""JXA"",IF(RC[-5]=""SPZ"",""SPX"",IF(RC[-5]=""SKQ"",""QSPX"",IF(RC[-5]=""SQP"",""QSPX"",
IF(RC[-2]=""AM"",""SPC"",IF(RC[-2]=""S&P"",""SP"",IF(RC[-2]=""EMINI"",""ES"",IF(RC[-2]=""IMM"",""EV"",IF(RC[-2]=""0810"",""SPC"",IF(RC[-2]=""0811"",""SPC"",IF(RC[-2]=""0812"",""SPC"",IF(RC[-2]=""0901"",""SPC"",IF(RC[-2]=""0902"",""SPC"",IF(RC[-2]=""0903"",""SPC"",IF(RC[-2]=""0904"",""SPC"",IF(RC[-2]=""0905"",""SPC"")))))))))))))))))))))))))))))))))))))))))))))))))))))))"
Range("G2").Select
Range("G1:I1").Select
Selection.Copy
Range("G2:I400").Select
ActiveSheet.Paste

Please let me know what is wrong with the IF statment. This IF statement
works perfectly when I just copy it into a cell. It only doesn't work when I
run it from a macro. Thanks!
Click to expand...
 
A

aileen

Jim,

That worked perfectly. Thanks!

Matt, I tried yours also, but it changed everything to "SPX".

Thanks to both of you for the quick response.

Jim Thomlinson said:
A string is limited in the number of characters you can have. Try splitting
the string into 2...

Range("G1").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-5]=""SPX"",""SPX"",IF(RC[-5]=""SXB"",""SPX"",IF(RC[-5]=""SPQ"",""SPX"",IF(RC[-5]=""SPT"",""SPX"",IF(RC[-5]=""SZP"",""SPX"",IF(RC[-5]=""SXY"",""SPX"",IF(RC[-5]=""SXZ"",""SPX"",IF(RC[-5]=""SXM"",""SPX"",IF(RC[-5]=""SPB"",""SPX"",IF(RC[-5]=""SVP"",""SPX"",IF(RC[-5]=""SZJ"",""SPX"",IF(RC[-5]=""SZV"",""SPX"",IF(RC[-5]=""SPL"",""SPX"",IF(RC[-5]=""SYZ"",""SPX"",IF(RC[-5]=""SXG"",""SPX"",IF(RC[-5]=""SZT"",""SPX"",IF(RC[-5]=""QSE"",""QSPX"",IF(RC[-5]=""SAQ"",""QSPX"",IF(RC[-5]=""SLQ"",""QSPX"",IF(RC[-5]=""SWV"",""SPY"",IF(RC[-5]=""SZC"",""SPY"",IF(RC[-5]=""SWG"",""SPY"",IF(RC[-5]=""SFB"",""SPY"",IF(RC[-5]=""SYH"",""SPY"",IF(RC[-5]=""SUE"",""SPY"",IF(RC[-5]=""FYS"",""SPY"",IF(RC[-5]=""FYN"",""SPY"",IF(RC[-5]=""YQA"",""SPY"",IF(RC[-5]=""YAZ"",""SPY"",IF(RC[-5]=""CYU"",""SPY"",IF(RC[-5]=""JCA"",""SPY"",IF(RC[-5]=""CYY"",""SPY"",IF(RC[-5]=""RDQ"",""QSPY"",IF(RC[-5]=""RQQ"",""QSPY"",IF(RC[-5]=""SPY"",""SPY"",IF(RC[-5]" & _
"=""SZQ"",""QSPX"",IF(RC[-5]=""JXD"",""JXA"",IF(RC[-5]=""JXE"",""JXA"",IF(RC[-5]=""JXA"","
"JXA"",IF(RC[-5]=""JXB"",""JXA"",IF(RC[-5]=""SPZ"",""SPX"",IF(RC[-5]=""SKQ"",""QSPX"",IF(RC[-5]=""SQP"",""QSPX"",
IF(RC[-2]=""AM"",""SPC"",IF(RC[-2]=""S&P"",""SP"",IF(RC[-2]=""EMINI"",""ES"",IF(RC[-2]=""IMM"",""EV"",IF(RC[-2]=""0810"",""SPC"",IF(RC[-2]=""0811"",""SPC"",IF(RC[-2]=""0812"",""SPC"",IF(RC[-2]=""0901"",""SPC"",IF(RC[-2]=""0902"",""SPC"",IF(RC[-2]=""0903"",""SPC"",IF(RC[-2]=""0904"",""SPC"",IF(RC[-2]=""0905"",""SPC"")))))))))))))))))))))))))))))))))))))))))))))))))))))))"
--
HTH...

Jim Thomlinson


aileen said:
I am copying some if statements with a macro into a workbook and when I have
the following code in the macro it works perfectly:

Range("H1").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-5]=""JAN"",""JAN"",IF(RC[-5]=""FEB"",""FEB"",IF(RC[-5]=""MAR"",""MAR"",IF(RC[-5]=""APR"",""APR"",IF(RC[-5]=""MAY"",""MAY"",IF(RC[-5]=""JUN"",""JUN"",IF(RC[-5]=""JUL"",""JUL"",IF(RC[-5]=""AUG"",""AUG"",IF(RC[-5]=""SEP"",""SEP"",IF(RC[-5]=""OCT"",""OCT"",IF(RC[-5]=""NOV"",""NOV"",IF(RC[-5]=""DEC"",""DEC""))))))))))))"
Range("I1").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-5]=8,2008,IF(RC[-5]=9,2009,IF(RC[-5]=10,2010,IF(RC[-5]=11,2011,IF(RC[-5]=12,2012,IF(RC[-5]=13,2013,IF(RC[-5]=14,2014,IF(RC[-5]=15,2015,IF(RC[-5]=16,2016,IF(RC[-5]=17,2017,IF(RC[-5]=18,2018,IF(RC[-5]=19,2019,IF(RC[-5]=20,2020)))))))))))))"
Range("K1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]=""C"",RC[-10],0)"
Range("L1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-6]=""P"",RC[-11],0)"
Range("L2").Select

But as soon as I add this IF statement I get a syntax error:

Range("G1").Select
ActiveCell.FormulaR1C1 = _
Click to expand...
"=IF(RC[-5]=""SPX"",""SPX"",IF(RC[-5]=""SXB"",""SPX"",IF(RC[-5]=""SPQ"",""SPX"",IF(RC[-5]=""SPT"",""SPX"",IF(RC[-5]=""SZP"",""SPX"",IF(RC[-5]=""SXY"",""SPX"",IF(RC[-5]=""SXZ"",""SPX"",IF(RC[-5]=""SXM"",""SPX"",IF(RC[-5]=""SPB"",""SPX"",IF(RC[-5]=""SVP"",""SPX"",IF(RC[-5]=""SZJ"",""SPX"",IF(RC[-5]=""SZV"",""SPX"",IF(RC[-5]=""SPL"",""SPX"",IF(RC[-5]=""SYZ"",""SPX"",IF(RC[-5]=""SXG"",""SPX"",IF(RC[-5]=""SZT"",""SPX"",IF(RC[-5]=""QSE"",""QSPX"",IF(RC[-5]=""SAQ"",""QSPX"",IF(RC[-5]=""SLQ"",""QSPX"",IF(RC[-5]=""SWV"",""SPY"",IF(RC[-5]=""SZC"",""SPY"",IF(RC[-5]=""SWG"",""SPY"",IF(RC[-5]=""SFB"",""SPY"",IF(RC[-5]=""SYH"",""SPY"",IF(RC[-5]=""SUE"",""SPY"",IF(RC[-5]=""FYS"",""SPY"",IF(RC[-5]=""FYN"",""SPY"",IF(RC[-5]=""YQA"",""SPY"",IF(RC[-5]=""YAZ"",""SPY"",IF(RC[-5]=""CYU"",""SPY"",IF(RC[-5]=""JCA"",""SPY"",IF(RC[-5]=""CYY"",""SPY"",IF(RC[-5]=""RDQ"",""QSPY"",IF(RC[-5]=""RQQ"",""QSPY"",IF(RC[-5]=""SPY"",""SPY"",IF(RC[-5]=""SZQ"",""QSPX"",IF(RC[-5]=""JXD"",""JXA"",IF(RC[-5]=""JXE"",""JXA"",IF(RC[-5]=""JXA"","
"JXA"",IF(RC[-5]=""JXB"",""JXA"",IF(RC[-5]=""SPZ"",""SPX"",IF(RC[-5]=""SKQ"",""QSPX"",IF(RC[-5]=""SQP"",""QSPX"",
IF(RC[-2]=""AM"",""SPC"",IF(RC[-2]=""S&P"",""SP"",IF(RC[-2]=""EMINI"",""ES"",IF(RC[-2]=""IMM"",""EV"",IF(RC[-2]=""0810"",""SPC"",IF(RC[-2]=""0811"",""SPC"",IF(RC[-2]=""0812"",""SPC"",IF(RC[-2]=""0901"",""SPC"",IF(RC[-2]=""0902"",""SPC"",IF(RC[-2]=""0903"",""SPC"",IF(RC[-2]=""0904"",""SPC"",IF(RC[-2]=""0905"",""SPC"")))))))))))))))))))))))))))))))))))))))))))))))))))))))"
Range("G2").Select
Range("G1:I1").Select
Selection.Copy
Range("G2:I400").Select
ActiveSheet.Paste

Please let me know what is wrong with the IF statment. This IF statement
works perfectly when I just copy it into a cell. It only doesn't work when I
run it from a macro. Thanks!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro to copy rows that contain a cell with a certain number 3
Macro 3
Creating Pivot Table with Macro using Multiple Sheets 0
Modify range in VBA 9
Different Results from the Same Macro 3
Macro: Copying to all possible rows 2
If function in Macro 11
changing range in recorded macro 1

Top