ExcelBanter - Find & replace

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

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

- - Find & replace (https://www.excelbanter.com/excel-programming/333991-re-find-replace.html)

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

--
steveB

Remove "AYN" from email to respond
wrote in message
ups.com...
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

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").Colum ns(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 wrote:
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

--
steveB

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

--
steveB

Remove "AYN" from email to respond
wrote in message
oups.com...
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").Colum ns(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 wrote:
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

--
steveB

"STEVE BELL" wrote in message
news:Gqzze.524$F96.263@trnddc08...
First make sure you define the variable cel.

Replace Worksheets with Sheets.

Why?

My bad...

cel had been defined...

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

Thanks for keeping me straight...

--
steveB

Remove "AYN" from email to respond
"Bob Phillips" wrote in message
...

"STEVE BELL" wrote in message
news:Gqzze.524$F96.263@trnddc08...
First make sure you define the variable cel.

Replace Worksheets with Sheets.

Why?

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

--
HTH

Bob Phillips

"STEVE BELL" wrote in message
news:9QAze.632$Zy6.278@trnddc04...
My bad...

cel had been defined...

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

Thanks for keeping me straight...

--
steveB

Remove "AYN" from email to respond
"Bob Phillips" wrote in message
...

"STEVE BELL" wrote in message
news:Gqzze.524$F96.263@trnddc08...
First make sure you define the variable cel.

Replace Worksheets with Sheets.

Why?

My bad (again)

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

--
steveB

Remove "AYN" from email to respond
"Bob Phillips" wrote in message
...
Yeah but Sheets applies to all sheets, worksheets, charts etc., and you
can't have ranges in a chart.

--
HTH

Bob Phillips

"STEVE BELL" wrote in message
news:9QAze.632$Zy6.278@trnddc04...
My bad...

cel had been defined...

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

Thanks for keeping me straight...

--
steveB

Remove "AYN" from email to respond
"Bob Phillips" wrote in message
...

"STEVE BELL" wrote in message
news:Gqzze.524$F96.263@trnddc08...
First make sure you define the variable cel.

Replace Worksheets with Sheets.

Why?

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

Bob

"STEVE BELL" wrote in message
news:ujBze.637$Zy6.189@trnddc04...
My bad (again)

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

--
steveB

Remove "AYN" from email to respond
"Bob Phillips" wrote in message
...
Yeah but Sheets applies to all sheets, worksheets, charts etc., and you
can't have ranges in a chart.

--
HTH

Bob Phillips

"STEVE BELL" wrote in message
news:9QAze.632$Zy6.278@trnddc04...
My bad...

cel had been defined...

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

Thanks for keeping me straight...

--
steveB

Remove "AYN" from email to respond
"Bob Phillips" wrote in message
...

"STEVE BELL" wrote in message
news:Gqzze.524$F96.263@trnddc08...
First make sure you define the variable cel.

Replace Worksheets with Sheets.

Why?