Creating a loop or calling a sub for each value in a range...

**dgo** · 12-21-2010, 07:40 AM

Hi,

I am having problems adding attachements, so I can't upload a spreadsheet - I will try explain this the best I can!

I have a spreadsheet with different tabs on. On the first tab called "Front" I have a list of identifiers in a range from N16 to N26. The amount of identifiers changed constantly. Sometimes there maybe 1, 4 or 0 etc.

These identifiers relate to data on another tab "Portfolio", that I want to copy into a further different tab "Index" and run a vlookup

So on the sheet "Front" within the range N16 to N26, I want to be able to run the following code (Macro 3) for any of the identifiers within that range, where ActiveCell is in this case the first cell in the range N16:

    ActiveCell.Copy
    Sheets("Index").Select
    Range("B4").Select
    Selection.End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ActiveCell.Offset(0, 1).FormulaR1C1 = "=VLOOKUP(RC[-1],Portfolio!C[-1]:C[16],2,FALSE)"
    ActiveCell.Offset(0, 2).FormulaR1C1 = "=VLOOKUP(RC[-2],Portfolio!C[-2]:C[15],3,FALSE)"
    ActiveCell.Offset(0, 8).FormulaR1C1 = "=VLOOKUP(RC[-8],Portfolio!C[-8]:C[8],9,FALSE)"
    ActiveCell.Offset(0, 9).FormulaR1C1 = "=VLOOKUP(RC[-9],Portfolio!C[-9]:C[8],10,FALSE)"
    ActiveCell.Offset(0, 13).FormulaR1C1 = "=VLOOKUP(RC[-13],Portfolio!C[-13]:C[3],14,FALSE)"
    ActiveCell.Offset(0, 17).FormulaR1C1 = "=VLOOKUP(RC[-17],Portfolio!C[-17]:C[3],18,FALSE)"

I am assuming there is some kind of loop that can do this that will run or exit loop if there are no identifiers, but I don't have any experience with loops. Either that or call this sub for each cell in a range.

Many thanks for taking the time to look - I will try again later to edit post and load a spreadsheet.

**royUK** · 12-21-2010, 08:34 AM

Have you tried zipping your file before attaching?

**dgo** · 12-21-2010, 09:38 AM

Aha! That has worked - thanks for the advice.

**royUK** · 12-21-2010, 10:03 AM

Why can't you do the VLOOKUP without the copying?

**dgo** · 12-21-2010, 10:10 AM

I need the identifier on the Index tab becuase that code is used on the Positions tab. So if you take a look at the Postions tab in column A, that takes the data from the "Index" tab, which includes the Identifier.

The number of rows on the Index tab changes, hence why there is the formula in column A in Positions.