putting a vlookup into vba code

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,036
Hi, good afternoon, hope you can help I have the code below which is part vlookup and part vba but it doesn't work, I want it to go into cell C2 then drag it down to the last column, but all it does is paste the same code into each cell and does nothing hope you can help.
Code:
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Private Sub CommandButton1_Click()[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim lr As Long[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][SIZE=3][COLOR=#000000][FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]lr = Cells(Rows.Count, "A").End(xlUp).Row[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]With Range("C2:C" & lr)[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][SIZE=3][COLOR=#000000].Formula ="=VLOOKUP(A2,Old!B:C,2,FALSE)"[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE]
[SIZE=3][COLOR=#000000].Value = .Value
[FONT=Times New Roman][/FONT]
  End With
[FONT=Times New Roman][/FONT][FONT=Times New Roman][/FONT]
End Sub
[FONT=Times New Roman][/FONT]

[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/CO[/COLOR][/SIZE][/FONT]DE]
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

WaterGypsy

Well-known Member
Joined
Jan 15, 2010
Messages
697
I would just put the code for the formula into C2, then use Autofill to copy it down. Something like:

Rich (BB code):
.
.
Range("C2").Value ="=VLOOKUP(A2,Old!B:C,2,FALSE)"
range("C2").Select
Selection.AutoFill Destination:=Range("C2:C" & Ir)
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,036
HI thank you for the response I have tried the code, thank you but I get an error on the line below, it gets highlighted in yellow
Code:
Selection.AutoFill Destination:=Range("C2:C" & Ir)
this is the whole code
Code:
Private Sub CommandButton1_Click()
  Dim lr As Long
  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet
  Set copySheet = Worksheets("Jun")
  Set pasteSheet = Worksheets("Jun")
  
  Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
  On Error Resume Next
  Range("AJ2:AJ" & Lastrow).SpecialCells(xlBlanks).Value = Range("AJ2").Value
  On Error GoTo 0
Range("AP2", Range("AP2").End(xlDown)).Copy Range("A2")
Range("A2", Range("A2").End(xlDown)).NumberFormat = "0"
Range("AB2", Range("AB2").End(xlDown)).Copy Range("E2")
Range("AD2", Range("AD2").End(xlDown)).Copy Range("F2")
Range("AO2", Range("AO2").End(xlDown)).Copy Range("G2")
Range("AG2", Range("AG2").End(xlDown)).Copy Range("H2")
Range("AJ2", Range("AJ2").End(xlDown)).Copy Range("I2")
Range("AS2", Range("AS2").End(xlDown)).Copy Range("M2")
Range("C2").Value = "=VLOOKUP(A2,Old!B:C,2,FALSE)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C" & Ir)
End Sub
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,036
hi again the error says ' Run-time error 1004, method range of object worksheet failed
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,737
Office Version
365
Platform
Windows
Try
Code:
Range("C2:C" & LastRow).Formula = "=VLOOKUP(A2,Old!B:C,2,FALSE)"
 

WaterGypsy

Well-known Member
Joined
Jan 15, 2010
Messages
697
My typo .... the Autofill needs to be to your last line variable rather than Ir
 

Watch MrExcel Video

Forum statistics

Threads
1,099,181
Messages
5,467,092
Members
406,523
Latest member
saravanantdct83

This Week's Hot Topics

Top