putting a vlookup into vba code

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,227
Office Version
  1. 2010
Platform
  1. Windows
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]
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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)
 
Upvote 0
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
 
Upvote 0
hi again the error says ' Run-time error 1004, method range of object worksheet failed
 
Upvote 0
Try
Code:
Range("C2:C" & LastRow).Formula = "=VLOOKUP(A2,Old!B:C,2,FALSE)"
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top