VBA code - Filter with Partial Criteria and replaced with row same line

[jmery24]

Hi, i am new in this forum. First sorry my poor english.


I'm trying to put together a macro that looks for a specific character and replace it
with another one that is located in another column of the same line.

Example
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[/TR]
[TR]
[TD]11/05/2018
[/TD]
[TD]Z123456
[/TD]
[TD]5
[/TD]
[TD]15000
[/TD]
[/TR]
[TR]
[TD]11/05/2018
[/TD]
[TD]Z123457
[/TD]
[TD]3
[/TD]
[TD]12500
[/TD]
[/TR]
[TR]
[TD]11/05/2018
[/TD]
[TD]5555628
[/TD]
[TD][/TD]
[TD]13800
[/TD]
[/TR]
[TR]
[TD]11/05/2018
[/TD]
[TD]123456
[/TD]
[TD][/TD]
[TD]5000
[/TD]
[/TR]
</tbody>[/TABLE]
I need in each cell that the number begins with the letter Z,
it is replaced by the number that is located in the column next to.


RESULT


[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[/TR]
[TR]
[TD]11/05/2018
[/TD]
[TD]5123456
[/TD]
[TD]5
[/TD]
[TD]15000
[/TD]
[/TR]
[TR]
[TD]11/05/2018
[/TD]
[TD]3123457
[/TD]
[TD]3
[/TD]
[TD]12500
[/TD]
[/TR]
[TR]
[TD]11/05/2018
[/TD]
[TD]5555628
[/TD]
[TD][/TD]
[TD]13800
[/TD]
[/TR]
[TR]
[TD]11/05/2018
[/TD]
[TD]123456
[/TD]
[TD][/TD]
[TD]5000
[/TD]
[/TR]
</tbody>[/TABLE]

Method 1
I was trying some methods, but I could not solve it correctly


Columns("B:B").Select
Selection.Replace What:="*Z", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Replacement is a fixed variable


Method 2

Dim cl As Range
For Each cl In Range("$B$2:$B" & Range("$B65536").End(xlUp).Row)
Select Case UCase(cl)
Case Is = "*Z": cl = Cells(cl.Row, 3) 'Column T value
Case Else
End Select
Next cl

It does not replace a single character but the entire cell

Thanks

Juan Manuel

 

[Fluff]

Hi & welcome to MrExcel.
How about

Dim cl As Range
For Each cl In Range("$B$2:$B" & Range("$B65536").End(xlUp).Row)
Select Case UCase(Left(cl, 1))
Case Is = "Z": cl.Value = Replace(cl.Value, "Z", cl.Offset(, 1).Value, 1, 1, vbTextCompare)
Case Else
End Select
Next cl

 

[jmery24]

Hi & welcome to MrExcel.
How about

Dim cl As Range
For Each cl In Range("$B$2:$B" & Range("$B65536").End(xlUp).Row)
Select Case UCase(Left(cl, 1))
Case Is = "Z": cl.Value = Replace(cl.Value, "Z", cl.Offset(, 1).Value, 1, 1, vbTextCompare)
Case Else
End Select
Next cl

Work Perfect. Thanks you very much

 

[Fluff]

Glad to help & thanks for the feedback