Macro Issues: Compile Error (Syntax) and Runtime error 1004 - Application Defined or Object Defined Error

M

mcgrupp

Board Regular
Joined
Jan 15, 2015
Messages
66
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-

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:
Sort by date Sort by votes
Hello,

Recording macros does have it's uses (learning stuff mainly), but generally they are not the ideal solution when it comes to creating macros you want to use. There are a few things you need to do before we can tackle this problem.

1. Remove all the Select Statements as follows.
Code: 
Sheets("Direct Deposit Audit Report").Select
    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(FIND(""Modified by"",RC[-1],1)),"""",""Modified"")"

will become
Code: 
Sheets("Direct Deposit Audit Report").Range("B2").FormulaR1C1 = _
        "=IF(ISERROR(FIND(""Modified by"",RC[-1],1)),"""",""Modified"")"

also i'm not sure what you're trying to achieve with this block of code... could you explain what the procedure and expected outcome is?

Code: 
Range("B2:D2").Copy
    Application.Goto Reference:="R2C2:R50000C4"
    ActiveSheet.Paste
    Columns("B:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues

Many Thanks
Caleeco
 
Upvote 0
Ok, I replaced the code as you suggested. The block of code is copying the formulas in B2:D2, pasting them from B2:D50000 and pasting them as values.
 
Upvote 0
Your macro code is missing some function names in formulas. Try replacing them as following:

Code: 
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),"" "",[COLOR=#0000ff]REPT("[/COLOR]" "",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),"" "",REPT(""" & _
    " "",99)),99))))"




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([COLOR=#0000ff]FIND(" & _
    """\[/COLOR]"",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)))))"
 
Last edited:
Upvote 0
That was it. So odd that would happen though. I am really appreciative of your help and you've given me something to look into as I still want to find the root cause of why those functions were missing.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Run-time error '1004': Application-defined or object-defined error While searching for shapes within a specified area in worksheet using VBA.
Replies
7
Views
109
NdNoviceHlp
NdNoviceHlp
Sean15
  • Question Question
Could you help improve recorded macro
2
Replies
13
Views
584
Sean15
Sean15
T
  • Question Question
ActiveSheet. Paste error
Replies
1
Views
404
RoryA
RoryA
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
2K
Joe4
Joe4
R
  • Question Question
"Out of Memory" Error when trying to pivot
Replies
1
Views
40
johnny51981
J

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top