Filling down formula to cover complete range
 

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
column and have the macro automatically copy the VLOOKUP down to ever
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 an
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

--
matp
-----------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=48178

Filling down formula to cover complete range
 

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" wrote in
message ...

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