VBA loop the place product names in a given ceel

[MIDBOND]

I run a report every month for dozens of products
the number of products differ each month (we add and remove products on a monthly basis)
the product names are in a worksheet called "cross reference" they start in cell GZ18 and go down to any line
I need to place the product name in cell EX20 in the worksheet "attribution report"
once the name is in cell EX20 a report is automatically run and stored in the separate worksheet.
right now I have the produce names hardcoded in a macro that repeats the same 10ish lines of code dozens of times (once for each product). I currently have to edit the code to remove the code for the deleted products and add code for the new products

I repeat the same code each time

what I want to do is is run a macro that places the first product name from starting from cell "cross reference" GZ18 into "attribution report" cell EX20
once there the report will run and be stored
I then want to go back to the list and put the next name until I reach the end of the list

 

[Scott T]

Sounds like you have worksheet change event code setup. Try this on a copy of your file comment out the change event ether move and modify the code into the loop below.

Sub loopprod()

Dim lr As Long
lr = Sheets("cross reference").Cells(Rows.Count, "GZ").End(xlUp).Row 'finds last row of product list


For x = 18 To lr 'loops though product names
    Sheets("attribution report").Range("EX20") = Sheets("cross reference").Range("GZ" & x) 'puts product name in cell

[COLOR=#008000]    'You could just have the code run off the cross reference sheet pulling the product name from Range("GZ"&x) and you would not need the line above[/COLOR]    
[COLOR=#008000]    'or put your run report code here[/COLOR]


Next x


End Sub

 

[MIDBOND]

Fantastic, many thanks.
I'm not that good with VBA, I kept putting the dots in the wrong spot
this is a terrific help

 

[Scott T]

If I understand what you need correctly the row that the code is on in equal to x. When x=18 then the product name in row 18 is pulled when it loops again then x=19 and the product name in GZ19 is used and so on

 

[MIDBOND]

the answer to the above question is yes; the loop is working great it has reduce the coding from dozens and dozens of line that have to be updated every time a product is added or deleted. The loop does away with all this in just two simple line.

However I have lost some info that I still need; I didn't realize this until I ran your code. All the products are coded as either a fruit or vegetable, and depending on that classification one of two different types of reports are run. The report part I know how to do but what I need now is the ability to know if the the product being picked up in the loop is a fruit or vegetable. To do this I already have each product coded in the cross reference table in the column beside the product name. So the product in GZ26 may be "apple", and beside that in the next column is "fruit". So if I knew which line the loop was on I would be able to use a vlookup to let the report know which type of report to run.

I though a way to do that would be to have the loop number displayed in a cell that I could use in the vlookup.

So as the loop runs the loop number X would appear in a cell and then change when the loop goes to the next line.

I hope this makes sense

 

[Scott T]

If Fruit or Vegetable is in the next column then you can pull the value in VBA then use it as need.

fruitorveg = Sheets("cross reference").Range("HA"&x)

Or
you can do the vlookup in VBA into a variable

fruitorveg = Application.VLookup(Sheets("cross reference").Range("GZ" & x), Sheets("sheetwherelookuprangeis").Range("A1:B2"), 2, 0)
If fruitorveg <> "fruit" Or fruitorveg <> "veg" Then [COLOR=#008000]'error checking to make sure name found. fruit or veg is case senitive so match your lookup table[/COLOR]
    [COLOR=#008000]'or use ucase to make everything upper case in vba.[/COLOR]
    MsgBox "product name not found"
    Exit Sub
End If