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

Column A	Column B	Column C	Column D
11/05/2018	Z123456	5	15000
11/05/2018	Z123457	3	12500
11/05/2018	5555628		13800
11/05/2018	123456		5000

<tbody>
</tbody>

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

Column A	Column B	Column C	Column D
11/05/2018	5123456	5	15000
11/05/2018	3123457	3	12500
11/05/2018	5555628		13800
11/05/2018	123456		5000

<tbody>
</tbody>

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