Solved

Runtime error 1004 application defined or object defined error

Posted on 2014-02-26
4
2,210 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

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.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
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…

751 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