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:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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

L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
931
Joe4
Joe4
T
  • Solved
Block If without End If
2
Replies
13
Views
637
kevin9999
K
J
  • Question
VBA Code to Add Rows is slowing down
Replies
5
Views
495
JayB0730
J
G
  • Question
How can i extract the correct leave time on this code?
Replies
1
Views
356
rlv01
R
C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
199
Coco1
C

Forum statistics

Threads
1,214,649
Messages
6,120,732
Members
448,987
Latest member
marion_davis

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