VBA macro to insert a vlookup into Excel returns error 1004, but the formula works when I copy and paste it.

[Leob]

I've written a macro to compare two sheets, by inserting vlookup functions on one of the sheets.

I've tried making the formula arguments a string or a variant (ActiveFormula), but it still doesn't work. It doesn't work for formula or formulaR1C1 (notation is correct in both cases), nor when I try selection.formula instead of activecell.formula.

When I run the macro, it returns error 1004: Application-defined or Object-Defined error, in the line activecell.formula = Activeformula.

I've used Debug.Print for Activeformula; when I copy and paste it into excel it works 100%.

If I use On Error Resume Next the macro generates the correct formula in every loop, but the macro fails when it should insert the formula into the cell.

See code below (the 3rd last line from the bottom is where it falls apart):

Option Explicit

Sub Match_LMS_To_BM_Backstamps()

Dim startmacro
'LMSTopRow is the row number of the headings on the LMS report
Dim LMSTopRow As Long
'LMSBotRow is the row number of the lowest full cell on the LMS report
Dim LMSBotRow As Long
'BMBotRow is the row number of the lowest full cell on the BM report
Dim BMBotRow As Long

'Make sure user wants to start and has the correct sheet selected
startmacro = MsgBox("Please make sure the LMS Volumes dispatched report is on a sheet called ''LMS Desp'' and the BM tracking extract (1-93-40-7) is on a sheet called ''BM POD EXTRACT''" & vbNewLine & vbNewLine & "Are you sure you wish to continue?", vbYesNo, "LMS to Bookmaster Mismatch Detector")
If startmacro = vbNo Then Exit Sub

'BMCols is an integer array storing column numbers of  (0) POA, (1) POH, (2) POD, (3)Customer reference
Dim BMCols(0 To 3) As Integer
'LMSCols is a variant array storing the strings of (0) "POA", (1) "POH", (2) "POD"
Dim LMSCols As Variant
LMSCols = Array("POA", "POH", "POD")

'make sure sheet is formatted correctly
Sheets("LMS Desp").Select
    If Range("C1").Formula = "PARCELID" Then
    LMSTopRow = 1
    ElseIf Range("C7").Formula = "PARCELID" Then
    LMSTopRow = 7
    Else
    MsgBox "Parcelid isn't in cell C3 or C7 - please make sure you have the correct sheet and that you've used an LMS Volumes-Despatched report."
    Exit Sub
    End If
'find the row number of the lowest cell on the LMS report
Cells(LMSTopRow, 3).Select
LMSBotRow = Selection.End(xlDown).Row

'Get column numbers on the BM report
Sheets("BM POD Extract").Select
Range("1:1").Select
        Selection.Find(What:="CUSTREF", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        BMCols(3) = ActiveCell.Column
Range("1:1").Select
        Selection.Find(What:="POA", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        BMCols(0) = ActiveCell.Column
Range("1:1").Select
        Selection.Find(What:="POH", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        BMCols(1) = ActiveCell.Column
Range("1:1").Select
        Selection.Find(What:="POD", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        BMCols(2) = ActiveCell.Column
Range("A1").Select
BMBotRow = Selection.End(xlDown).Row
        
Sheets("LMS Desp").Select
'find cust ref column for the vlookup
Rows(LMSTopRow).Select
Selection.Find(What:="CUSTOMERREFERENCE", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Select
'copies customer reference to the second column, so the vlookups are unaffected when columns are inserted
        Range(ActiveCell, Cells(LMSBotRow, Selection.Column)).Select
        Selection.Copy
        Cells(LMSTopRow, 2).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
'Uses a For loop to insert columns for comparison and vlookups
Dim k As Integer
For k = 0 To 2
Rows(LMSTopRow).Select
        Selection.Find(What:=LMSCols(k) & "DATE", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Select
ActiveCell.Formula = ActiveCell.Formula & " LMS"
'insert column from BM sheet
Selection.EntireColumn.Insert
ActiveCell.Formula = LMSCols(k) & "DATE BM"
ActiveCell.Offset(1, 0).Activate
Dim ActiveFormula As String
'Below formula is in A1 notation
'ActiveFormula = "=vlookup(B" & Selection.Row & ",'BM POD Extract'!" & Cells(1, BMCols(3)).Address & ":" & Cells(BMBotRow, BMCols(2)).Address & "," & (BMCols(2) - BMCols(3) + k) & ",0"
'Below formula is in R1C1 notation
ActiveFormula = "= vlookup(R" & Selection.Row & "C2,'BM POD Extract'!R1C" & BMCols(3) & ":R" & BMBotRow & "C" & BMCols(2) & "," & (BMCols(2) - BMCols(3) + k - 1) & ",0"
Debug.Print ActiveFormula
Selection.FormulaR1C1 = ActiveFormula

Next

End Sub

The vlookup on the first run generates R1C1 = "= vlookup(R8C2,'BM POD Extract'!R1C3:R3783C8,5,0" or A1 = "=vlookup(B8,'BM POD Extract'!$C$1:$H$3783,5,0" and should put it into cell D8.

Please help!

 

[Yard]

Try

ActiveFormula = "VLOOKUP(R" & Selection.Row & "C2,'BM POD Extract'!R1C" & BMCols(3) & ":R" & BMBotRow & "C" & BMCols(2) & "," & (BMCols(2) - BMCols(3) + k - 1) & ",0"
Debug.Print ActiveFormula
Selection.FormulaR1C1 = "=" & ActiveFormula

 

[Leob]

Still no luck, returns the same error

 

[Yard]

Think it may be to do with using Select and Selection.

In practice you hardly ever need to select things to manipulate them.

Looking back up your code, although you Activate the cell in which you want the formula, I'm not 100% certain this is the same as using Select. Therefore you may be trying to insert the formula into the entire row. Still not quite clear why that wouldn't work, but I can't see another obvious reason (although it almost certainly IS obvious).

edit: found it I think : you're missing the bracket off the end!

 

[RoryA]

Assuming that selection.row is the row you are entering the formula, you don't need a number. Also, as Yard added, you are missing the closing parenthesis:

ActiveFormula = "= vlookup(RC2,'BM POD Extract'!R1C" & BMCols(3) & ":R" & BMBotRow & "C" & BMCols(2) & "," & (BMCols(2) - BMCols(3) + k - 1) & ",0)"
Debug.Print ActiveFormula

 

[Leob]

Ah.. didn't think about the bracket.

Well spotted, thank you! It's working perfectly

Do I assign points to you or anything like that?

I'm really liking the expertise available on this site!

 

[RoryA]

A simple thanks is fine.
(there's no points system, or marking threads as Solved or anything here)

 

[Leob]

All right then... Thanks for all the help