ExcelBanter - "Translating" values in a cell

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - "Translating" values in a cell (https://www.excelbanter.com/excel-programming/322962-translating-values-cell.html)

"Translating" values in a cell

Hi there, first post.

I have a workbook with 3 sheets, and i would like to be able to enter a
certain value into a cell on the first page and have excel return a row
of data from the second page to the third page.

On the first sheet, I want to enter a code like "AB123". The
corresponding data is in on the second sheet in column A, in a format
like "VBA__2Y123". In either format, the final numbers are always 3
digits long and are identical. On the first sheet, the "AB" portion is
always a two-letter code. On the second sheet, the "VBA__2Y" portion is
always 7 characters long.

Can I do something like use a "Let" statement?

For example:
Let Left(format1,2)= "AB" = Left(format2,7) = "VBA__2Y"

Am I totally out in left field on this?

Any suggestions that you might share would be greatly appreciated. As
you can tell, I am really new to VBA...

Thanks!

"Translating" values in a cell

The "Usual" method for looking up a value in a list is the VLookup function:

In Sheet1 you enter in A1 --- "AB123"
In B1 is a formula like =VLOOKUP(A1, Sheet2!$A1:F100,3,0)
This will lookup AB123 in Sheet2 Range A1:A100 ( the first column) and then
return to B1 the value that is in column C -- the third col
You would then copy B1 down as far as needed.

Now, can add to your Vlookup formula something like:
=VLOOKUP(Left(A1,2), Sheet2!$A1:F100,3,0)
which will look for Left(A1,2) or "AB" in Col A of Sheet2
I'm not sure what the connection is in you example between AB and VBA__2Y
but if you can make the connection then some adaptation of the above lookup
should work for you.
Hope this helps

" wrote:

Hi there, first post.

I have a workbook with 3 sheets, and i would like to be able to enter a
certain value into a cell on the first page and have excel return a row
of data from the second page to the third page.

On the first sheet, I want to enter a code like "AB123". The
corresponding data is in on the second sheet in column A, in a format
like "VBA__2Y123". In either format, the final numbers are always 3
digits long and are identical. On the first sheet, the "AB" portion is
always a two-letter code. On the second sheet, the "VBA__2Y" portion is
always 7 characters long.

Can I do something like use a "Let" statement?

For example:
Let Left(format1,2)= "AB" = Left(format2,7) = "VBA__2Y"

Am I totally out in left field on this?

Any suggestions that you might share would be greatly appreciated. As
you can tell, I am really new to VBA...

Thanks!

"Translating" values in a cell

Thanks for your comment Andrew.

Apologies for the lack of clarity.

What I want to do is the following:
On sheet1, I type "AB123" into cell A1.
Then I run a macro that searches for the cell with the same value (but
in the format "VBA_123") on Sheet2.
Once it finds the cell, the macro copies the contents of that entire
row, and pastes the copied data on Sheet3.

Regards,

CH

"Translating" values in a cell

Can you explain further when you say your macro "searches for the cell with
the same value (but in the format "VBA_123")

I'm not sure I understand how "AB123" is the same value as "VBA_123"

Do you mean that you macro searches for the Right 3 characters (that's the
only part that is the same value)
If this is your intent, then does something like this work?

Sheet2.Range("A:A").Find (What:=Right(Sheet1!A1,3))

" wrote:

Thanks for your comment Andrew.

Apologies for the lack of clarity.

What I want to do is the following:
On sheet1, I type "AB123" into cell A1.
Then I run a macro that searches for the cell with the same value (but
in the format "VBA_123") on Sheet2.
Once it finds the cell, the macro copies the contents of that entire
row, and pastes the copied data on Sheet3.

Regards,

CH

"Translating" values in a cell

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.

"Translating" values in a cell

C

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.

You're going to need a table that maps old prefixes to new. That table
could be in memory as the macro runs, or in another worksheet. Based on
what I've read about your situation, I would add a column to sheet2 with a
formula that converts the partnumber using a table in Sheet4 (new sheet).
Then use the Find method to get that new part number.

On sheet2:

VBA__2Y123 other info AB123

the last column would be
=VLOOKUP(LEFT(A3,7),Sheet4!A1:B30,2,FALSE)&RIGHT(A 3,3)

Now you can Find based on the last column and if you find it, copy the whole
row (except the last column) to sheet3.

If you can't change Sheet2 and want to do it all in code, you might do this

Sub CopyRow()

Dim sNewPart As String
Dim sOldPart As String
Dim rFound As Range

sNewPart = Sheet1.Range("B1").Value

sOldPart = ConvertPart(sNewPart)

If Len(sOldPart) 0 Then
Set rFound = Sheet2.Columns(1).Find(sOldPart)

If Not rFound Is Nothing Then
rFound.EntireRow.Copy Sheet3.Range("A65536").End(xlUp).Offset(1,
0)
End If
End If

End Sub

Function ConvertPart(ByRef sNew As String) As String

Dim vaOld As Variant
Dim vaNew As Variant
Dim vMatch As Variant

vaOld = Array("VBA__2Y", "VGA__3X", "HIJ__5C")
vaNew = Array("AB", "AG", "MT")

vMatch = Application.Match(Left(sNew, 2), vaNew, False)

If Not IsError(vMatch) Then
ConvertPart = vaOld(vMatch - 1) & Right(sNew, 3)
Else
ConvertPart = ""
End If

End Function

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

"Translating" values in a cell

Thank you all very much.

Dick, that is a tremendous help and is exactly what I was hoping to do.

THANK YOU!