Looping is for Chumps <g Don't do it if you don't have to.

The data gets to the return cell because I put your formula in the cell -
actually every cell in column A for every used cell in column D all in one
command. (You have the formula correctly set up so the 2 for the row will
adjust properly for each row it is entered).

I then replace the formula with the value it displays. (Again all in one
command)

To summarize, I do this all in one fell swoop by determinging how many rows
are utilized in column D. (as you asked)

--
Regards,
Tom Ogilvy




"hshayh0rn" wrote in message
...
I appreciate your reply time. I'm not sure I understand how this code will
work for me. How does the returned value get to the right cell and how
does
the code loop until there is no more data to use?

"Tom Ogilvy" wrote:

Dim rng as Range, rng1 as Range
set rng = Range(Cells(2,"D"),Cells(rows.count,"D").End(xlup) )
set rng1 = rng.offset(0,-3)
rng1.Formula = "=IF(ISERROR(VLOOKUP($D2,'XXX User
Report'!$1:$65536,2,FALSE)),"""",VLOOKUP($D2,'XXX User
Report'!$1:$65536,2,FALSE))"
' to remove the formulas and replace with the value returned
' uncomment the next line
'rng1.Formula = rng1.Value

Adjust the rng1.Formula line(s) to fix the word wrap. Note the """" in
place of ""

--
Regards,
Tom Ogilvy


"hshayh0rn" wrote in message
...
I have some worksheets that use a Vlookup statement to get information
from
other worksheets. Right now I have the statement below repeated for
1000
rows. I'd like to remove these statement and complete the work
entirely in
VBA code. One of the reasons for this is to make the speadsheet a
little
quicker in some instances and more dynamic in others. Generally we
don't
have
the need for 1000 rows BUT we never truly know how many rows we need
and
just
yesterday we needed 1010 rows which meant I had to send out a revised
sheadsheet to that particular user. Could anyone like to help with
with
the
code I need to get the same results I would get below? The statement
below
appears in cell A2 on a worksheet call "consolidate sheet" and is
repeated
for 1000 row but as I mentioned I need it to populate the cells in
column
A
of the "consolidated sheet" until there is no more data to get.

=IF(ISERROR(VLOOKUP($D2,'XXX User
Report'!$1:$65536,2,FALSE)),"",VLOOKUP($D2,'XXX User
Report'!$1:$65536,2,FALSE))


Thank you for your help in advance.