Autofill Macro

[olly_w]

Hi guys

I've got a formula I need to run using a macro, and need it to run from Row 3 of the worksheet to the last used row.

The formula is =vlookup, J3, $BI:$BJ, 2, FALSE) - with the J3 reference incrementing (i.e. J4, J5 etc).

Any ideas on how best to run this using VBA? Any help greatly appreciated.

Regards

Olly

 

[SteveO59L]

Why would you want to ?

 

[Datsmart]

You didn't say what column you wanted the formula in, I choose K, adjust as needed.

Range("K3:K" & ActiveSheet.UsedRange.Rows.Count).Formula = _
        "=VLOOKUP(J3,$BI:$BJ, 2, FALSE)"

 

[olly_w]

Guys this is great, thanks.

Is there a way to nest something into the VBA code so that, once the lookup has been performed, that "paste special" is carried out on these cells so that the formula is removed and just the values left?

Cheers

Olly

 

[olly_w]

Guys this is great, thanks.

Is there a way to nest something into the VBA code so that, once the lookup has been performed, that "paste special" is carried out on these cells so that the formula is removed and just the values left?

Cheers

Olly

 

[Datsmart]

Just add this line after the formula line:

Range("K3:K" & ActiveSheet.UsedRange.Rows.Count).Value = _ 
Range("K3:K" & ActiveSheet.UsedRange.Rows.Count).Value