Find & replace

[koobelek]

Here is my little problem:

1st spreadsheet
===============

Column A | Column B
---------+----------
AAA(Tab) | 80001
BBB(Tab) | 80002
CCC(Tab) | 80003
XXX(Tab) | 80004
YYY(Tab) | 80005
ZZZ(Tab) | 80006

2nd spreadsheet
===============

Column A
--------
AAA
AAA
AAA
AAA
BBB
BBB
BBB
BBB
CCC
CCC
YYY
YYY
ZZZ
ZZZ
ZZZ
XXX
XXX

Expected result:
----------------

2nd spreadsheet
===============

Column A
--------
80001
80001
80001
80001
80002
80002
80002
80002
80003
80003
80005
80005
80006
80006
80006
80004
80004

I need to translate column A in the 2nd spreadsheet to numbers from the
1st spreadsheet (column B). I would appreciate any suggestion.

Regards,
koobel

 

[Bob Phillips]

in a separate column

=INDEX(Sheet1!A:A,MATCH(A1,Sheet1!B:B,0))

and copy down

 

[STEVE BELL]

Try this (code untested).
Caution it will error out if the value of cel is not found on Sheet1

Dim cel As Range

For Each cel In Sheets("Sheet2").Columns(1)
If Len(cel) > 0 Then
cel.Value = WorksheetFunction.Index(Sheets("Sheet1").Columns(2), _
Match(cel, Sheets("Sheet1"), 0), 1)
ElseIf Len(cel) = 0 Then
Exit Sub
End If
End Sub

You can build a safeguard
if worksheetfunction.Countif(Sheets("Sheet1").Columns(1),cel)>0 then

end if

 

[koobelek]

Dim cel As Range

Sub ConvertMaterialToId()

For Each cel In Worksheets("Sheet2").Columns(1)
If Len(cel) > 0 Then
cel.Value =
WorksheetFunction.Index(Worksheets("Sheet1").Columns(2),
WorksheetFunction.Match(cel, Sheets("Sheet1"), 0), 1)
Else
If Len(cel) = 0 Then Exit Sub
End If
Next

End Sub

I get an error message "Subscript out of Range". What's wrong with that
piece of code?

 

[STEVE BELL]

First make sure you define the variable cel.

Replace Worksheets with Sheets.

Next replace "Sheet1" and "Sheet2" with the actual names of the worksheets -
"MySheet", "Data", "Master", "MyData" or what ever they are labled.
Worksheets("Sheet1"). becomes Sheets("MySheet").
[If the position of the worksheets will not change - you can use Sheets(1),
Sheets(2), etc
indicating the position of the worksheet in your workbook]
Worksheets("Sheet1"). becomes Sheets(1).

Make sure the the cel.value formula is one line - since it is very long I
have
used the line continuation " _" in the code below.

See if this works better. Let me know...


Sub ConvertMaterialToId()
Dim cel As Range
For Each cel In Sheets("Sheet2").Columns(1)
If Len(cel) > 0 Then
cel.Value = WorksheetFunction.Index(Sheets("Sheet1").Columns(2), _
WorksheetFunction.Match(cel, Sheets("Sheet1"), 0), 1)
Else
If Len(cel) = 0 Then Exit Sub
End If
Next

End Sub

 

[Bob Phillips]

First make sure you define the variable cel.

Replace Worksheets with Sheets.

Why?

 

[STEVE BELL]

My bad...

cel had been defined...

and I just prefer Sheets... (because it is shorter)

Thanks for keeping me straight...

 

[Bob Phillips]

Yeah but Sheets applies to all sheets, worksheets, charts etc., and you
can't have ranges in a chart.

 

[STEVE BELL]

My bad (again)

I rarely (if ever) work with chart sheets and forget the distinction...

 

[Bob Phillips]

Me neither, so I always use Worksheets, and when I do use charts I usually
forget Sheets and have an initial problem <vbg>

Bob