How do I return a cell address in a variable

I have a "Products Database" formatted in an Excel Worksheet. The size is 27 columns by approx 2200 rows. In another section of the worksheet I have a single column with Product ID numbers. I would like to write a macro that will go through the single column list and compare each Product ID number with the Product IDs in my data base. When a match occurs, I want to change a value in the Products Database in the same row (different column) as the found ProductID.

I am fairly new to Excel, but am a retired Programmer with over 20 years of experience and am just looking for a little guidance. Is there a worksheet function that will do a Vertical Lookup and return the row number when a value matches?

Thanks,
Klaus Cook
Houston, Texas

Klaus

The worksheetfunction MATCH will give you the row number for each of the matching product ids. You could either use that directly in the spreadsheet to return the cell address, or use it in a macro.

Another way is to use the find function to see if there is a match to the id. If you set the result to a variable, you could then use offset to update the relevant cell, or use something like

Please Login or Register  to view this content.

Like most things in Excel, there are probably various ways to achieve your result. Working out which is "best" for you is the hard part.


rylo