Solved

Runtime error 1004 application defined or object defined error

Posted on 2014-02-26
4
2,199 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
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 a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

763 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