Solved

Runtime error 1004 application defined or object defined error

Posted on 2014-02-26
4
2,122 Views
Last Modified: 2014-02-26
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
0
Comment
Question by:boukaka
  • 2
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
mvidas earned 500 total points
ID: 39889753
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
 

Author Comment

by:boukaka
ID: 39889779
That's fantastic, I'll just wait for you to post and award the points. Thank you so much for replying so quickly.
0
 
LVL 35

Expert Comment

by:mvidas
ID: 39889802
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
 

Author Closing Comment

by:boukaka
ID: 39889816
Perfect response! Thank you.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now