Replacing multiple numerical values

[Mati44]

I would like to replace the values in sheet1 from the corresponding values in Column B of sheet2 in the attached excel file.
Can I get a little help please?

I found the VBA code, but I think it needs a little adjustment.

Thank you!

[mana]

lookat:=xlWhole

[Mati44]

Thanks, Mana.

I did the following change :


Sub Test()

Dim Sh1 As Worksheet
Dim Sh2 As Worksheet

Set Sh1 = Sheets(1)
Set Sh2 = Sheets(2)
For Each cel In Sh2.Columns(1).SpecialCells(2)
With Sh1.Cells
Set c = .Find(cel, lookat:=xlWhole)
Do
If Not c Is Nothing Then
c.Value = Replace(c, cel, cel.Offset(, 1))
End If
Set c = .FindNext(c)
Loop Until c Is Nothing
End With
Next
End Sub

but I still have problem with single digit numbers.

[mana]

c.Value = Replace(c, cel, cel.Offset(, 1) & "@")




.Replace "@", "", xlPart

[Mati44]

I'm sorry, I didn't understand what you meant.

I tried this but this doesn't work :
c.Value = .Replace("@", "", xlPart)

[mana]

1 ---> 3@ --->3
2 ---> 5@ --->5


With Sh1.Cells
For Each cel In Sh2.Columns(1).SpecialCells(2)
Set c = .Find(cel, lookat:=xlWhole)
Do
If Not c Is Nothing Then
c.Value = Replace(c, cel, cel.Offset(, 1) & "@")
End If
Set c = .FindNext(c)
Loop Until c Is Nothing
Next
.Replace "@", "", xlPart
End With
End Sub

[mana]

Sub Test2()
Dim Sh1 As Worksheet
Dim Sh2 As Worksheet

Set Sh1 = Sheets(1)
Set Sh2 = Sheets(2)

With Sh1.Cells
For Each cel In Sh2.Columns(1).SpecialCells(2)
.Replace cel, cel.Offset(, 1) & "@", xlWhole
Next
.Replace "@", "", xlPart
End With
End Sub

[Mati44]

Thanks, Mana. It works for all numbers now.