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!
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.
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)
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
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.