Filling down formula to cover complete range

[matpj]

Hi,

this is something I will quite often have to do.
Where I havea range of data, and want to put, say, a VLOOKUP in a column and have the macro automatically copy the VLOOKUP down to every row that has data - how can I acheive this?

my example.
I need a macro to insert two columns C and D
the data spans from A to AB.

once they have been inserted, i need to put the following in C2 and then copy it down.

=VLOOKUP(B2,[sheamcat.xls]Sheet1!C1:C4, 3, 0)

and a similar thing into D2

=VLOOKUP(B2,[sheamcat.xls]Sheet1!C1:C4, 4, 0)

any ideas?

[Bob Phillips]

Sub FillDownFormulae()
Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("C2").Formula = "=VLOOKUP(B2,[sheamcat.xls]Sheet1!C1:C4, 3, 0)"
Range("C2").AutoFill Range("C2").Resize(iLastRow - 1)
Range("D2").Formula = "=VLOOKUP(B2,[sheamcat.xls]Sheet1!C1:C4, 4, 0)"
Range("D2").AutoFill Range("D2").Resize(iLastRow - 1)

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"matpj" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> this is something I will quite often have to do.
> Where I havea range of data, and want to put, say, a VLOOKUP in a
> column and have the macro automatically copy the VLOOKUP down to every
> row that has data - how can I acheive this?
>
> my example.
> I need a macro to insert two columns C and D
> the data spans from A to AB.
>
> once they have been inserted, i need to put the following in C2 and
> then copy it down.
>
> =VLOOKUP(B2,[sheamcat.xls]Sheet1!C1:C4, 3, 0)
>
> and a similar thing into D2
>
> =VLOOKUP(B2,[sheamcat.xls]Sheet1!C1:C4, 4, 0)
>
> any ideas?
>
>
> --
> matpj
> ------------------------------------------------------------------------
> matpj's Profile:
http://www.excelforum.com/member.php...o&userid=21076
> View this thread: http://www.excelforum.com/showthread...hreadid=481783
>