Long commands raise error in Excel

Question

I recorded a VBA code without any issues, but now when am trying to use the VBA code with the same data I get this compile error:

syntax error in an IF and OR formula.

I think the length of the code is what is causing the errors.

This is the formula that I wrote when I was recording:

=IF(OR(B20="(Number of results = 1)",B19="(Number of results = 1)",B18="(Number of results = 1)",B17="(Number of results = 1)",B16="(Number of results = 1)",B15="(Number of results = 1)",B14="(Number of results = 1)",B13="(Number of results = 1)",B12="(Number of results = 1)",B11="(Number of results = 1)",B10="(Number of results = 1)",B9="(Number of results = 1)",B8="(Number of results = 1)",B7="(Number of results = 1)",B6="(Number of results = 1)",B5="(Number of results = 1)",B4="(Number of results = 1)",B3="(Number of results = 1)",B2="(Number of results = 1)"),"True","F")

And this is part of the recorded code (its a long VBA code so I just copied the part with problem that was highlighted above (with yellow color)).

ActiveWindow.ScrollRow = 1
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B33").Select
Columns("A:A").ColumnWidth = 26
ActiveWindow.SmallScroll Down:=-63
Range("A1").Select
Application.FormulaBarHeight = 7
ActiveCell.FormulaR1C1 = _
"=IF(OR(R[19]C[1]=""(Number of Results = 1)"",R[18]C[1]=""(Number of Results =   1)"",R[17]C[1]=""(Number of Results = 1)"",R[16]C[1]=""(Number of Results = 1)"",R[15]C[1]=""(Number of Results = 1)"",R[14]C[1]=""(Number of Results = 1)"",R[13]C[1]=""(Number of Results = 1)"",R[12]C[1]=""(Number of Results = 1)"",R[11]C[1]=""(Number of Results = 1)"",R[10]C[1]=""(Number"& _
"lts = 1)"",R[9]C[1]=""(Number of Results = 1)"",R[8]C[1]=""(Number of Results = 1)"",R[7]C[1]=""(Number of Results = 1)"",R[6]C[1]=""(Number of Results = 1)"",R[5]C[1]=""(Number of Results = 1)"",R[4]C[1]=""(Number of Results = 1)"",R[3]C[1]=""(Number of Results = 1)"",R[2]C[1]=""(Number of Results = 1)"",R[1]C[1]=""(Number of Results = 1)""),""True"",""F"")""& _
"k
Range("A2").Select
Application.FormulaBarHeight = 1
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A202")
Range("A1:A202").Select

Yes I can see the syntax error? What exactly are you trying to do? Or can you update the question with the exact code the macro recorder gave you? — Siddharth Rout
Iam trying to use an IF and OR formula to filter and give a specific value only when the value in the cell is (Number of Results = 1) and it works fine when I use it, but when I record it and try it with the same data it give me this error, when I checked the recorded macro in the VBA there is a difference in the formula. — user3024248

shA.t shA.t · Accepted Answer · 2015-04-11T13:54:07

You are using macro recorder that is an option to give you your needed code, But here when you have a long string of formula it make a mistake code; like that you have after ActiveCell.FormulaR1C1 =, so you should manually edit it.

Note :
Formula in an Excel cell is different from formula in VBA code !!!

The maximum string that can come in front of that line of code is something like this:

ActiveCell.FormulaR1C1 = "=""" + String(255, "#") + """"

And

ActiveCell.FormulaR1C1 = "=""" + String(256, "#") + """"

Will return this error:

Run-time error '1004':
Application-defined or object-defined error

Well, the only solution that I can suggest is just changing and minimizing you formula string, and for you I suggest using this formula:

=IF(IFERROR(VLOOKUP("(Number of Results = 1)";B:B;1;0);"Err") = "Err"; "F"; "True")

Long commands raise error in Excel

1 Answers