VBA for vlookup with dynamic range

[blnswamy]

Hi
I am trying to write a code with vlookup. The purpose of the code is to reference a data set in another sheet using vlookup and show the corresponding values. The data sets may may be varying in sizes. So i want the vlookup to go to the last cell possible with the data.

I have tried these two ways of calling vlookup, but nothing seems to work.
1.

  With Worksheets(2).Range("B1")                            
        .Formula = "=vlookup(A1,Sheet1!'$A1:$F50,1,FALSE)"       
        .Value = .Value
  End With
  Range("B1").Select
  Selection.Autofill Destination:=Range("B1:B50")

2.

Range("B1").Select
ActiveCell.FormulaR1C1 = "=Vlookup(A1,Sheet1!'$A1:$F50,1,FALSE)"
Range("B1").Select
Selection.Autofill Destination:=Range("B1:B50")

In sheet 2 range can change depending on the data set. that is what i want to be flexible. The autofill range also needs to be flexible, which i am unable to do.

 

[blnswamy]

i have also used this code to have dynamic range of cells

 Range("B1").Select
        With Selection
            .Formula = "=vlookup(a1,Postupdate!'$A1:$F50,1,FALSE)"
            With .Resize(Range("B1" & Rows.Count).End(xlUp).Row - 1)
                    .FillDown
                    .Copy
                    .PasteSpecial x1PasteValues
                End With
            End With

but it fails at .resize line