VBA Question - Help!

[madab]

Hi - hoping something might be able to help me with this one...
What I need to do is the following: insert a number of vlookups into an excel worksheet, autofill to the last row of data, copy and paste special values to remove the formulae. The vlookups need to start after the last column of data in row 3 (i.e. the number of columns may not be fixed each time). I have been able to write a macro that will put the vlookups into each cell in each column. However I can't figure out how to autofill the vlookup's to be last row of data. I'd normally use something along the lines of: Dim x as long, x = range("A65000").End(xlup).Row, Range("????").Autofill Destination:=Range("???" & x)

Any ideas?

Thanks

 

[ztodd]

For what it's worth, once you start using VBA to insert formulas into cells, there's a good chance you're making it all too complicated and can probably simplify how you've got it all set up.

 

[RoryA]

What's the code you have so far?

 

[ParamRay]

.
.

Perhaps you can adapt something like this:

Sub InsertFunction()

    Dim lst_col As Integer
    Dim lst_row As Long
    Dim row_num As Long
    
    With Worksheets(1)
        lst_col = .Cells(2, .Columns.Count).End(xlToLeft).Column
        lst_row = .Cells(.Rows.Count, lst_col).End(xlUp).Row
    
        For row_num = 3 To lst_row
            .Cells(row_num, lst_col + 1).Formula = _
                "=VLOOKUP(" & .Cells(row_num, lst_col).Address(False, False) & "," & _
                Worksheets(2).Name & "!A:B,2,FALSE)"
        Next row_num
    End With

End Sub