PDA

View Full Version : Big IF Loop Question



j.smith1981
02-23-2009, 05:29 AM
I have a big big problem.

I am wanting to make a macro loop within a loop (or at least years ago when I attempted something like this it was a loop within a loop).

The thing is (I have attached the 2 files for peoples reference).

I want the macro to take the '!PRODUCTCODE' column for each cell, go to the data.xls file, find the corresponding 'product code' in the first column.

Then move to the right and select 'Manufacturer' this would then auto input a slash '/' and then input the cells value to the right Printer.

If the product code is the same below, like same column but the row down, has the same value as the preceding cell, then this would add a new column after categories and do the same again.

Repeating the process down the list, how would this be possible please?

Thanks in advance,
Jeremy.

Bob Phillips
02-23-2009, 05:32 AM
No need for a second loop, just use the Find method. Check it in help.

j.smith1981
02-23-2009, 05:36 AM
No need for a second loop, just use the Find method. Check it in help.

Ah thanks for that, always seem to go for Loops I know I shouldnt but sometimes its just easier, but I shall have a go at doing it using Find, thanks again, if I get stuck i'll try and make a sample excel file(s) and show you, thanks ever so much again.

Jeremy.

j.smith1981
02-23-2009, 05:55 AM
What im failing to understand is how would I make it go down one row without it being in a loop though?

Thats the whol essence of this macro is to get it to look at the numerous same product codes and bring back the values of the corrsponding cells.

Bob Phillips
02-23-2009, 06:18 AM
Lookup Find in help as suggested.

j.smith1981
02-23-2009, 06:25 AM
I will explain again (see attached):

in productsExample.xls: What i want it to do is, go down !PRODUCTCODE column find the matching !PRODUCTCODE in dataExample.xls.

When it finds the corresponding code in the first colum in dataExample.xls

It then copies the value of column B and C and formats it with the Manufactuer colum value with a prefixed delimiter '/' and then the Printer column value of the corresponding cell.

It then looks down the !PRODUCTCODE, and if it finds the same code, repeats the process but adds another column into productsExample.xls with the new details of that result.

The attachement is with this post

j.smith1981
02-23-2009, 06:27 AM
Here's the first file

j.smith1981
02-23-2009, 06:27 AM
and here's the 2nd

mikerickson
02-23-2009, 07:17 AM
There's no need for a macro or looping, putting
=VLOOKUP(RC[-13],[dataExample.xls]Sheet1!C1:C3,2,FALSE) & "/" & VLOOKUP(RC[-13],[dataExample.xls]Sheet1!C1:C3,3,FALSE)

in productExamples.xls R2C14 and dragging down should do what you want.