putting a vlookup into vba code

[Patriot2879]

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.

[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]

 

[WaterGypsy]

I would just put the code for the formula into C2, then use Autofill to copy it down. Something like:

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

 

[Patriot2879]

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

Selection.AutoFill Destination:=Range("C2:C" & Ir)

this is the whole 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]

hi again the error says ' Run-time error 1004, method range of object worksheet failed

 

[Fluff]

Try

Range("C2:C" & LastRow).Formula = "=VLOOKUP(A2,Old!B:C,2,FALSE)"

 

[Patriot2879]

this is great many thanks for your help

 

[WaterGypsy]

My typo .... the Autofill needs to be to your last line variable rather than Ir

 

[Fluff]

this is great many thanks for your help

You're welcome & thanks for the feedback