Runtime error 1004 application defined or object defined error

I have a small macro that is manipulating columns and entering formula's in a couple of cells but for some reason when I try to set the vlookup formula I get a runtime error. (at this line  ActiveCell.FormulaR1C1 = Formula)

If I paste the string that is in Formula into the cell manually the formula is fine and works perfectly. Can someone please tell me why I can't paste it in using vba code?


Sub Macro1()
   
    Sheets("Sheet2").Select
    Dim Check As String
    Check = "D1:D" + CStr(LastRow(Sheets("Sheet2")))
    Columns("A:A").Select
    Selection.Cut
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight
    Columns("A:B").Select
    Selection.NumberFormat = "@"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-2])"
    Selection.AutoFill Destination:=Range(Check)
    Sheets("Sheet1").Select
    Range("A1").Select
    ActiveCell.EntireColumn.Insert
    Dim DestRange As String
    DestRange = "A3:" + CStr(LastRow(Sheets("Sheet1")))
    Dim Formula As String
    Dim Rng As String
    Rng = "$D$1:$E$" + CStr(LastRow(Sheets("Sheet2")))
    Formula = "=VLOOKUP(CONCATENATE(C2,D2),Sheet2!" + Rng + ",2,FALSE)"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = Formula
    Selection.AutoFill Destination:=Range(DestRange)
    Range("A1").Select
    ActiveCell.Value = "NEW_UCID"
    Range("B1").Select
    ActiveCell.Value = "OLD_UCID"
   
End Sub

Function LastRow(ws As Object) As Long

        Dim rLastCell As Object
        On Error GoTo ErrHan
        Set rLastCell = ws.Cells.Find("*", ws.Cells(1, 1), , , xlByRows, _
                                      xlPrevious)
        LastRow = rLastCell.Row

ErrExit:
        Exit Function

ErrHan:
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbExclamation, "LastRow()"
        Resume ErrExit

    End Function
boukakaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mvidasCommented:
The short answer is to change .FormulaR1C1 to .Formula. You're not using R1C1 notation, so it is erroring out.

I'll clean up your subroutine to get rid of the .select statements and similar, it will speed things up a bit.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
boukakaAuthor Commented:
That's fantastic, I'll just wait for you to post and award the points. Thank you so much for replying so quickly.
0
mvidasCommented:
Also, your setting of DestRange is forgetting the second :A
 DestRange = "A3:" + CStr(LastRow(Sheets("Sheet1")))

I took out the use of your variables and just put the function call in your macro lines:
Sub Macro1()
    
    Sheets("Sheet2").Columns("A").Cut
    Sheets("Sheet2").Columns("F").Insert
    Sheets("Sheet2").Columns("A:B").NumberFormat = "@"
    Sheets("Sheet2").Range("D1:D" & CStr(LastRow(Sheets("Sheet2")))).FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-2])"
    
    Sheets("Sheet1").Columns("A").Insert
    Sheets("Sheet1").Range("A1").Value = "NEW_UCID"
    Sheets("Sheet1").Range("B1").Value = "OLD_UCID"
    Sheets("Sheet1").Range("A2:A" & CStr(LastRow(Sheets("Sheet1")))).Formula = _
        "=VLOOKUP(CONCATENATE(C2,D2),Sheet2!$D$1:$E$" & CStr(LastRow(Sheets("Sheet2"))) & ",2,FALSE)"
    
End Sub

Open in new window

Matt
0
boukakaAuthor Commented:
Perfect response! Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Languages-Other

From novice to tech pro — start learning today.