Syntax error excel formula VBA

Question

I've been trying to find the error in an excel formula that when I enter it in a single cell it works and also if I copy it to other cells it works, but when I record that into a macro and I run it it fails and shows an error saying there's a syntax error in the formula. Here's the code and also how it looks in excel:

Sub Macrotest()
'
' Macrotest Macro
'

'
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(IF(RC[-8]<>R[-1]C[-8],IF(LEFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0)-1),3)=""Jul"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MAT"& _
        """,Sheet3!C[-9],0)),IF(LEFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0)-1),3)=""Aug"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Aug"","& _
        "-9],0)),IF(LEFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0)-1),3)=""Sep"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Sep"",Sheet3!C[-9]"& _
        "EFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0)-1),3)=""Oct"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Oct"",Sheet3!C[-9],0)),IF(LEFT"& _
        "eet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0)-1),3)=""Nov"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Nov"",Sheet3!C[-9],0)),IF(LEFT(INDEX(Sheet"& _
        "TCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0)-1),3)=""Dec"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Dec"",Sheet3!C[-9],0)),"" "")))))),"" "")), "" "", IF("& _
        "[-1]C[-8],IF(LEFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0)-1),3)=""Jul"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Jul"",Sheet3!C[-"& _
        "(LEFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0)-1),3)=""Aug"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Aug"",Sheet3!C[-9],0)),IF(LE"& _
        "Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0)-1),3)=""Sep"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Sep"",Sheet3!C[-9],0)),IF(LEFT(INDEX(She"& _
        "MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0)-1),3)=""Oct"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Oct"",Sheet3!C[-9],0)),IF(LEFT(INDEX(Sheet1!C[5],MAT"& _
        "RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0)-1),3)=""Nov"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Nov"",Sheet3!C[-9],0)),IF(LEFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC["& _
        "C[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0)-1),3)=""Dec"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&"" Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Dec"",Sheet3!C[-9],0)),"" "")))))),"" ""))""& _
        "
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J117")
    Range("J2:J117").Select
    ActiveWindow.SmallScroll Down:=-18
    Range("A1").Select
End Sub

Excel:

=IF(ISNA(IF(B2<>B1,IF(LEFT(INDEX(Sheet1!O:O,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0)-1),3)="Jul",INDEX(Sheet1!P:P,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0))/INDEX(Sheet3!B:B,MATCH("Jul",Sheet3!A:A,0)),IF(LEFT(INDEX(Sheet1!O:O,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0)-1),3)="Aug",INDEX(Sheet1!P:P,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0))/INDEX(Sheet3!B:B,MATCH("Aug",Sheet3!A:A,0)),IF(LEFT(INDEX(Sheet1!O:O,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0)-1),3)="Sep",INDEX(Sheet1!P:P,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0))/INDEX(Sheet3!B:B,MATCH("Sep",Sheet3!A:A,0)),IF(LEFT(INDEX(Sheet1!O:O,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0)-1),3)="Oct",INDEX(Sheet1!P:P,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0))/INDEX(Sheet3!B:B,MATCH("Oct",Sheet3!A:A,0)),IF(LEFT(INDEX(Sheet1!O:O,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0)-1),3)="Nov",INDEX(Sheet1!P:P,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0))/INDEX(Sheet3!B:B,MATCH("Nov",Sheet3!A:A,0)),IF(LEFT(INDEX(Sheet1!O:O,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0)-1),3)="Dec",INDEX(Sheet1!P:P,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0))/INDEX(Sheet3!B:B,MATCH("Dec",Sheet3!A:A,0))," "))))))," ")), " ", IF(B2<>B1,IF(LEFT(INDEX(Sheet1!O:O,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0)-1),3)="Jul",INDEX(Sheet1!P:P,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0))/INDEX(Sheet3!B:B,MATCH("Jul",Sheet3!A:A,0)),IF(LEFT(INDEX(Sheet1!O:O,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0)-1),3)="Aug",INDEX(Sheet1!P:P,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0))/INDEX(Sheet3!B:B,MATCH("Aug",Sheet3!A:A,0)),IF(LEFT(INDEX(Sheet1!O:O,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0)-1),3)="Sep",INDEX(Sheet1!P:P,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0))/INDEX(Sheet3!B:B,MATCH("Sep",Sheet3!A:A,0)),IF(LEFT(INDEX(Sheet1!O:O,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0)-1),3)="Oct",INDEX(Sheet1!P:P,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0))/INDEX(Sheet3!B:B,MATCH("Oct",Sheet3!A:A,0)),IF(LEFT(INDEX(Sheet1!O:O,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0)-1),3)="Nov",INDEX(Sheet1!P:P,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0))/INDEX(Sheet3!B:B,MATCH("Nov",Sheet3!A:A,0)),IF(LEFT(INDEX(Sheet1!O:O,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0)-1),3)="Dec",INDEX(Sheet1!P:P,MATCH(RIGHT(B2,LEN(B2)-SEARCH(" ",B2))&","&" "&LEFT(B2,SEARCH(" ",B2)-1)&"
 Total",Sheet1!G:G,0))/INDEX(Sheet3!B:B,MATCH("Dec",Sheet3!A:A,0))," "))))))," "))

at the end "& _" ... I think you can remove the 2nd to last " — Scott Holtzman
I think the statement should just end at ,"" ""))", i.e. get rid of the final " & _ " — YowE3K
But I then get an "1004 Application-defined or object-defined error" if I try to use that to set the cell formula — YowE3K
There's definitely problems on the 7th row of the formula code (starting with "[-1]C[-8]) as there is no preceding "R" at the end of the 6th row. That row also ends with a C[- but nothing at the start of the 8th row to finish that off. — YowE3K

YowE3K YowE3K · Accepted Answer · 2016-07-21T20:43:56

I started from your Excel formula and used a text editor to convert it to

ActiveCell.FormulaR1C1 = _
  "=IF(ISNA(IF(RC[-8]<>R[-1]C[-8],IF(LEFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&""Total"",Sheet1!C[-3],0)-1),3)=""Jul"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&" & _
  """Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Jul"",Sheet3!C[-9],0)),IF(LEFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&""Total"",Sheet1!C[-3],0)-1),3)=""Aug"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&" & _
  """Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Aug"",Sheet3!C[-9],0)),IF(LEFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&""Total"",Sheet1!C[-3],0)-1),3)=""Sep"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&" & _
  """Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Sep"",Sheet3!C[-9],0)),IF(LEFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&""Total"",Sheet1!C[-3],0)-1),3)=""Oct"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&" & _
  """Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Oct"",Sheet3!C[-9],0)),IF(LEFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&""Total"",Sheet1!C[-3],0)-1),3)=""Nov"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&" & _
  """Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Nov"",Sheet3!C[-9],0)),IF(LEFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&""Total"",Sheet1!C[-3],0)-1),3)=""Dec"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&" & _
  """Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Dec"",Sheet3!C[-9],0)),"" "")))))),"" "")), "" "", IF(RC[-8]<>R[-1]C[-8],IF(LEFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&""Total"",Sheet1!C[-3],0)-1),3)=""Jul"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&" & _
  """Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Jul"",Sheet3!C[-9],0)),IF(LEFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&""Total"",Sheet1!C[-3],0)-1),3)=""Aug"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&" & _
  """Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Aug"",Sheet3!C[-9],0)),IF(LEFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&""Total"",Sheet1!C[-3],0)-1),3)=""Sep"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&" & _
  """Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Sep"",Sheet3!C[-9],0)),IF(LEFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&""Total"",Sheet1!C[-3],0)-1),3)=""Oct"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&" & _
  """Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Oct"",Sheet3!C[-9],0)),IF(LEFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&""Total"",Sheet1!C[-3],0)-1),3)=""Nov"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&" & _
  """Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Nov"",Sheet3!C[-9],0)),IF(LEFT(INDEX(Sheet1!C[5],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&""Total"",Sheet1!C[-3],0)-1),3)=""Dec"",INDEX(Sheet1!C[6],MATCH(RIGHT(RC[-8],LEN(RC[-8])-SEARCH("" "",RC[-8]))&"",""&"" ""&LEFT(RC[-8],SEARCH("" "",RC[-8])-1)&" & _
  """Total"",Sheet1!C[-3],0))/INDEX(Sheet3!C[-8],MATCH(""Dec"",Sheet3!C[-9],0)),"" "")))))),"" ""))"

After executing that in cell J2, I compared the formula produced with your Excel formula again and verified that it says the same thing.

Syntax error excel formula VBA

1 Answers