I am having two errors occur when I try to run a macro I recorded. When I try to run the macro, I get a compile error: syntax. When I take out the formula in the macro causing the error, the macro runs but then I get a Runtime error 1004 - Application Defined or Object Defined error for another formula. When I take that formula out, the macro runs fine from start to finish. I was searching around for solutions and tried opening up a new Module and pasting the macro into there but that didn't do anything. The entire macro is posted below along with the two snippets causing the issues. The formulas causing the issues are also posted. Any help would be appreciated.
Compile Error (syntax)
Formula-=IF(K2="","",IF(ISERROR(LEFT(K2,FIND("Checking",K2,1)-2)),TRIM(RIGHT(SUBSTITUTE(LEFT(LEFT(K2,FIND("Savings",K2,1)-2),FIND(M2,LEFT(K2,FIND("Savings",K2,1)-2),1)-2)," ",REPT(" ",99)),99)),TRIM(RIGHT(SUBSTITUTE(LEFT(LEFT(K2,FIND("Checking",K2,1)-2),FIND(M2,LEFT(K2,FIND("Checking",K2,1)-2),1)-2)," ",REPT(" ",99)),99))))
Code-
Runtime error 1004 - Application Defined or Object Defined Error
Formula--=IF(F2="","",IF(OR(TRIM(RIGHT($A2,6))="Taxx",TRIM(RIGHT($A2,14))="Direct Deposit",TRIM(RIGHT($A2,9))="Checkss",TRIM(RIGHT($A3,6))="Taxx",TRIM(RIGHT($A3,14))="Direct Deposit",TRIM(RIGHT($A3,9))="Checkss",TRIM(RIGHT($A4,6))="Taxx",TRIM(RIGHT($A4,14))="Direct Deposit",TRIM(RIGHT($A4,9))="Checkss"),"",IF(ISERROR(FIND("/",$A2,1)),MID($A2,FIND(" ",$A2,1)+1,100),TRIM(MID(MID($A2,FIND(" ",$A2,1)+1,100),FIND(" ",MID($A2,FIND(" ",$A2,1)+1,30),1),100)))))
Code--
Entire Macro
Compile Error (syntax)
Formula-=IF(K2="","",IF(ISERROR(LEFT(K2,FIND("Checking",K2,1)-2)),TRIM(RIGHT(SUBSTITUTE(LEFT(LEFT(K2,FIND("Savings",K2,1)-2),FIND(M2,LEFT(K2,FIND("Savings",K2,1)-2),1)-2)," ",REPT(" ",99)),99)),TRIM(RIGHT(SUBSTITUTE(LEFT(LEFT(K2,FIND("Checking",K2,1)-2),FIND(M2,LEFT(K2,FIND("Checking",K2,1)-2),1)-2)," ",REPT(" ",99)),99))))
Code-
Code:
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",IF(ISERROR(LEFT(RC[-1],FIND(""Checking"",RC[-1],1)-2)),TRIM(RIGHT(SUBSTITUTE(LEFT(LEFT(RC[-1],FIND(""Savings"",RC[-1],1)-2),FIND(RC[1],LEFT(RC[-1],FIND(""Savings"",RC[-1],1)-2),1)-2),"" "",REPT("" "",99)),99)),TRIM(RIGHT(SUBSTITUTE(LEFT(LEFT(RC[-1],FIND(""Checking"",RC[-1],1)-2),FIND(RC[1],LEFT(RC[-1],FIND(""Checking"",RC[-1],1)-2),1)-2),"" "",R"& _
"",99)),99))))"
Runtime error 1004 - Application Defined or Object Defined Error
Formula--=IF(F2="","",IF(OR(TRIM(RIGHT($A2,6))="Taxx",TRIM(RIGHT($A2,14))="Direct Deposit",TRIM(RIGHT($A2,9))="Checkss",TRIM(RIGHT($A3,6))="Taxx",TRIM(RIGHT($A3,14))="Direct Deposit",TRIM(RIGHT($A3,9))="Checkss",TRIM(RIGHT($A4,6))="Taxx",TRIM(RIGHT($A4,14))="Direct Deposit",TRIM(RIGHT($A4,9))="Checkss"),"",IF(ISERROR(FIND("/",$A2,1)),MID($A2,FIND(" ",$A2,1)+1,100),TRIM(MID(MID($A2,FIND(" ",$A2,1)+1,100),FIND(" ",MID($A2,FIND(" ",$A2,1)+1,30),1),100)))))
Code--
Code:
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]="""","""",IF(OR(TRIM(RIGHT(RC1,6))=""Taxx"",TRIM(RIGHT(RC1,14))=""Direct Deposit"",TRIM(RIGHT(RC1,9))=""Checkss"",TRIM(RIGHT(R[1]C1,6))=""Taxx"",TRIM(RIGHT(R[1]C1,14))=""Direct Deposit"",TRIM(RIGHT(R[1]C1,9))=""Checkss"",TRIM(RIGHT(R[2]C1,6))=""Taxx"",TRIM(RIGHT(R[2]C1,14))=""Direct Deposit"",TRIM(RIGHT(R[2]C1,9))=""Checkss""),"""",IF(ISERROR(" & _
""",RC1,1)),MID(RC1,FIND("" "",RC1,1)+1,100),TRIM(MID(MID(RC1,FIND("" "",RC1,1)+1,100),FIND("" "",MID(RC1,FIND("" "",RC1,1)+1,30),1),100)))))"
Entire Macro
Code:
Sub DirectDepositAuditReport()
'
' DirectDepositAuditReport Macro
' Takes BCAR pasted into Direct Deposit Audit Report spreadsheet and extracts content to be audited
'
'
Sheets("Direct Deposit Audit Report").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(FIND(""Modified by"",RC[-1],1)),"""",""Modified"")"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(ISERROR(FIND(""Checking"",RC[-2],1)),ISERROR(FIND(""Savings"",RC[-2],1))),"""",IF(ISERROR(FIND(""Checking"",RC[-2],1)),""Savings"",""Checking""))"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]="""","""",IF(MID(RC1,5,1)<>""-"","""",IF(FIND("" "",RC1,1)<>10,"""",IF(MIN(FIND({0,1,2,3,4,5,6,7,8,9},LEFT(RC[-3],10)&""0123456789""))<=10,RC1,""""))))"
Range("B2:D2").Select
Selection.Copy
Application.Goto Reference:="R2C2:R50000C4"
ActiveSheet.Paste
Columns("B:D").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("E3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""",R[-1]C,RC[-1])"
Range("E3").Select
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="R3C5:R50000C5"
ActiveSheet.Paste
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(LEFT(RC1,4)=""NEW "",MID(RC1,7,1)=""/""),""NEW"",IF(LEFT(RC1,8)=""DELETED "",""DELETED"",IF(LEFT(RC1,8)=""UPDATED "",""UPDATED"","""")))"
Range("F2").Select
Selection.Copy
Application.Goto Reference:="R2C6:R50000C6"
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G2").Select
ActiveCell.FormulaR1C1 = "=RC[1]"
Range("G3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""",R[-1]C,RC[1])"
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]="""","""",IF(OR(TRIM(RIGHT(RC1,6))=""Taxx"",TRIM(RIGHT(RC1,14))=""Direct Deposit"",TRIM(RIGHT(RC1,9))=""Checkss"",TRIM(RIGHT(R[1]C1,6))=""Taxx"",TRIM(RIGHT(R[1]C1,14))=""Direct Deposit"",TRIM(RIGHT(R[1]C1,9))=""Checkss"",TRIM(RIGHT(R[2]C1,6))=""Taxx"",TRIM(RIGHT(R[2]C1,14))=""Direct Deposit"",TRIM(RIGHT(R[2]C1,9))=""Checkss""),"""",IF(ISERROR(" & _
""",RC1,1)),MID(RC1,FIND("" "",RC1,1)+1,100),TRIM(MID(MID(RC1,FIND("" "",RC1,1)+1,100),FIND("" "",MID(RC1,FIND("" "",RC1,1)+1,30),1),100)))))"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H3"), Type:=xlFillDefault
Range("G3:H3").Select
Selection.Copy
Application.Goto Reference:="R3C7:R50000C8"
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("I2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC8="""","""",IF(ISERROR(INDEX(RC1:R[30]C1,MATCH(R1C9,RC3:R[30]C3,0))),"""",IF(RC8<>(INDEX(RC7:R[30]C7,MATCH(R1C9,RC3:R[30]C3,0))),"""",INDEX(RC1:R[30]C1,MATCH(R1C9,RC3:R[30]C3,0)))))"
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC8="""","""",IF(ISERROR(INDEX(RC1:R[30]C1,MATCH(R1C10,RC3:R[30]C3,0))),"""",IF(RC8<>(INDEX(RC7:R[30]C7,MATCH(R1C10,RC3:R[30]C3,0))),"""",INDEX(RC1:R[30]C1,MATCH(R1C10,RC3:R[30]C3,0)))))"
Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-2]<>"""",RC[-1]<>"""")=FALSE,CONCATENATE(RC[-2],RC[-1]),IF(MATCH(RC[-2],RC[-10]:R[30]C[-10],0)<MATCH(RC[-1],RC[-10]:R[30]C[-10],0),RC[-2],RC[-1]))"
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",IF(ISERROR(LEFT(RC[-1],FIND(""Checking"",RC[-1],1)-2)),TRIM(RIGHT(SUBSTITUTE(LEFT(LEFT(RC[-1],FIND(""Savings"",RC[-1],1)-2),FIND(RC[1],LEFT(RC[-1],FIND(""Savings"",RC[-1],1)-2),1)-2),"" "",REPT("" "",99)),99)),TRIM(RIGHT(SUBSTITUTE(LEFT(LEFT(RC[-1],FIND(""Checking"",RC[-1],1)-2),FIND(RC[1],LEFT(RC[-1],FIND(""Checking"",RC[-1],1)-2),1)-2),"" "",R"& _
"",99)),99))))"
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]="""","""",IF(ISERROR(LEFT(RC[-2],FIND(""Checking"",RC[-2],1)-2)),TRIM(RIGHT(SUBSTITUTE(LEFT(RC[-2],FIND(""Savings"",RC[-2],1)-2),"" "",REPT("" "",99)),99)),TRIM(RIGHT(SUBSTITUTE(LEFT(RC[-2],FIND(""Checking"",RC[-2],1)-2),"" "",REPT("" "",99)),99))))"
Range("I2:M2").Select
Range("M2").Activate
Selection.Copy
Application.Goto Reference:="R2C9:R50000C13"
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("N2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(RC8="""","""",IF(ISERROR(INDEX(RC1:R[38]C1,MATCH(R1C14,RC2:R[38]C2,0))),"""",IF(RC8<>(INDEX(RC7:R[38]C7,MATCH(R1C14,RC2:R[38]C2,0))),"""",IF(TRIM(INDEX(RC1:R[38]C1,MATCH(R1C14,RC2:R[38]C2,0)))=""Modified by"",CONCATENATE(INDEX(RC1:R[38]C1,MATCH(R1C14,RC2:R[38]C2,0)),"" "",INDEX(RC1:R[38]C1,(MATCH(R1C14,RC2:R[38]C2,0)+1))),INDEX(RC1:R[38]C1,MATCH(R1C14,RC2:R[38]C2,0))))))"
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",IF(ISERROR(FIND("" "",TRIM(MID(RC[-1],13,30)),1)),TRIM(MID(RC[-1],13,30)),LEFT(TRIM(MID(RC[-1],13,30)),FIND("" "",TRIM(MID(RC[-1],13,30)),1)-1)))"
Range("P2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",IF(ISERROR(VLOOKUP(RC15,Username!C2:C3,1,0)),"""",""YES""))"
Range("Q2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",IF(VLOOKUP(RC[-2],Username!C2:C3,2,0)=0,"""",""TERMINATED""))"
Range("N2:Q2").Select
Selection.Copy
Application.Goto Reference:="R2C14:R50000C17"
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Instructions").Select
End Sub
Last edited: