using vlookup in a loop

C

chrismv48

Basically I have 2 sheets, "invoice" and "data".

I'm pulling data out of the data sheet and populating fields in the invoice.
I've been using vlookup to pull the data from the "data" sheet, populate the
invoice using the vlookup, do stuff with the invoice sheet (which i've built
a macro for already), and then I'd manually increase the target value by one
to go onto the next row.

I need a macro that will loop by adding +1 to the target value in my vlookup
formula and keep looping until there are no rows left.

Alternatively it'd be useful if a dialogue box would let the user tell the
macro where to start and end with the loop...if that's feasible.

any suggestions?
 
L

Lars-Åke Aspelin

Basically I have 2 sheets, "invoice" and "data".

I'm pulling data out of the data sheet and populating fields in the invoice.
I've been using vlookup to pull the data from the "data" sheet, populate the
invoice using the vlookup, do stuff with the invoice sheet (which i've built
a macro for already), and then I'd manually increase the target value by one
to go onto the next row.

I need a macro that will loop by adding +1 to the target value in my vlookup
formula and keep looping until there are no rows left.

Alternatively it'd be useful if a dialogue box would let the user tell the
macro where to start and end with the loop...if that's feasible.

any suggestions?


Please give some more information to make it easier to help:

1) What does your VLOOKUP statement look like right now
2) What do you mean by "target value"
3) What do you mean by "there are no rows left"

Lars-Åke
 
C

chrismv48

Okay I'm trying to set a user defined range to use in VLOOKUP. So for
starters, I'd like the user to select the range, then to have that range used
in the vlookup formula that's already stored in a cell, E3.

That way the user can decide what he/she wants to use as data.

Once that vlookup range is set, to let's say A1:B10 by the user, we need to
have a loop run that will increment the target cell each time. example:

-the formula in cell E3 starts as "=VLOOKUP(C$10$,UserRange,5,False)"
-at this point i have another macro to do other things, and at completion
the loop starts over so the target cell used in VLOOKUP (C10) will need to
interate by +1 until the last row in UserRange is reached...so if currently
cell C10 reads:

=July!A10 'the loop would add to the range each time by 1. So:
=July!A11
=July!A12
=July!A13

Until it reaches the end of the Vlookup range.

Sorry if that was confusing--I realize now that the way I explained it
initially was inaccurate.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vlookup code problem 2
Array To View Specific Sheet(s) 2
Copy Sheet and rename it. 11
Copy a range instead of just one row but with condition 8
vlookup 2
Macro Active Page Name 7
vlookup issue 2
Odd VLOOKUP Error 1

Top