how to drag vlookup in vba for all the cells down with dynamic range

how to drag vlookup in vba for all the cells down
I have written

Cells(4, Newcolumn).Formula = Application.WorksheetFunction.VLookup(Range("A4"), Sheets("Basic_Data").Range("C2:J7129"), 8, False)

Selection.AutoFill Destination:=Cells(newrange & ":" & r & "7129")
Range(newrange & ":" & r & "7129").Select

but this is copying only values not formula. I need to use dynamic range

Microsoft Excel

Last Comment

royhsiao

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

FernandoFernandes

3/10/2011

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

royhsiao

3/10/2011

here is another 2 ways to find the last rows

Sub Macro1()
Dim i As Integer
    
    i = ActiveSheet.UsedRange.Rows.Count
 
    MsgBox i
    
End Sub


Sub macro2()
Dim i As Integer
    i = Range("C65536").End(xlUp).Row
    MsgBox i

End Sub

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Certified Expert Program

Credly Partnership

Udemy Partnership