PDA

View Full Version : Replacing multiple numerical values



Mati44
11-06-2019, 12:31 AM
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
11-06-2019, 03:33 AM
lookat:=xlWhole

Mati44
11-06-2019, 04:03 AM
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
11-06-2019, 04:16 AM
c.Value = Replace(c, cel, cel.Offset(, 1) & "@")




.Replace "@", "", xlPart

Mati44
11-06-2019, 04:23 AM
I'm sorry, I didn't understand what you meant.

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

mana
11-06-2019, 04:37 AM
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
11-06-2019, 04:59 AM
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
11-06-2019, 05:23 AM
Thanks, Mana. It works for all numbers now.