Re: "Translating" values in a cell

Tech-Archive recommends: Fix windows errors by optimizing your registry

c_humble_at_hotmail.com
Date: 02/12/05 

Date: 12 Feb 2005 07:21:59 -0800

gocush, thanks very much for your assistance.

chances are that I inadvertently made things more confusing than need
be. apologies for that.

The background for this problem is as follows. There are new codes for
the products that i deal with at work that people have become
accustomed to using, but our internal system still uses the old codes.
The final three digits are the same in either code, but the new code
uses a simple 2-letter prefix, whereas the old system uses a 7-letter
prefix. I would like to make a macro where we can use the new code in
Sheet1, to find the corresponding old code in Sheet2 (which has the
product number in the old code in Column A, then miscellaneous other
data in the following columns), copy the entire row, and then paste it
into Sheet3.

The Vlookup function might be appropriate, but there are a large number
of products so I was hoping to come up with something a little more
elegant and wanted to avoid the hassle of building the table. More
importantly, when we get new products our internal system still books
them in the old code, which means that i would have to update the
vlookup table every time we get a new product.

Using the Right() command would work for most cases; however, there are
some products that have different prefixes but have the same last 3
digits. So, that is why I was hoping to find some way to make a
statement (or definition, or something) that would tell my macro that
if it sees "AB..." on the Sheet1, it should look for "VBA__2Y..." on
Sheet2. Then, I imagine that the macro would then need something such
as a Right() statement to match the final 3 digits, then the macro
could select the row, copy and paste the data to Sheet3.

I honestly thought that this was just a simple command that I had not
heard of yet and that the answer would be a one-liner. Very sorry for
all of the confusion, but greatly appreciate your assistance.

Quantcast