runtime error 1004 - application defined or object-defined error

[boukaka]

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

 

[par60056]

try using & instead of + when building strings

 

[par60056]

I don't thing the variable "formula" contains what you think it should. Try printing it in the debugger.

 

[boukaka]

I don't thing the variable "formula" contains what you think it should. Try printing it in the debugger.

I figured it out - I needed to use .Formula, not .FormulaR1C1 - it works now.

 

[par60056]

Way to go. Feels good.