VBA: Replace X with X @ Col X

[harky]

Hi, i need some help. I try to google but see hard for me

In myweeksht;
At Column D
1. Find WPxx And Replace with 'Poster'
2. Find number (the number can be single or double digital) And replace with 'Route'

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Col D[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Col D (Result)[/TD]
[/TR]
[TR]
[TD="align: center"]WP1[/TD]
[TD="align: center"]find WP @ Col D & replace with 'Poster[/TD]
[TD="align: center"]Poster[/TD]
[/TR]
[TR]
[TD="align: center"]WP2[/TD]
[TD="align: center"]''[/TD]
[TD="align: center"]Poster[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Any numbers found @ Col D will replace with 'Route'[/TD]
[TD="align: center"]Route[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Route[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Route[/TD]
[/TR]
</tbody>[/TABLE]


2nd part is
In myweeksht;
At Column E
Find Index and replace Col D to Index


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Col D[/TD]
[TD="align: center"]Col E[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Index[/TD]
[TD="align: center"]

find Index @ Col E & replace with with 'index' at Col D​

[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Index[/TD]
[TD="align: center"]find Index @ Col E & replace with with 'index' at Col D[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Index[/TD]
[TD="align: center"]

find Index @ Col E & replace with with 'index' at Col D​

[/TD]
[/TR]
</tbody>[/TABLE]

  With Range("Q:Q")
       .Replace "Yes", "=xxx", xlWhole, , False, , False, False
      .SpecialCells(xlFormulas, xlErrors).FormulaR1C1 = "=rc[-9]"
      .Value = .Value
      End With

 

[mole999]

try xlpart not xlwhole as that refers to the whole cell

 

[Yongle]

Sub [COLOR=#ff0000]FirstPart[/COLOR]()
    Dim cel As Range, ws As Worksheet
    Set ws = Sheets("[I][COLOR=#ff0000]myweeksht[/COLOR][/I]")
    
    For Each cel In ws.Range("D2", ws.Range("D" & ws.Rows.Count).End(xlUp))
        Select Case LCase(Left(cel, 2))
            Case "wp":          cel = "Poster"
            Case 0 To 99:       cel = "Route"
        End Select
    Next cel
End Sub

 

[harky]

Hi this work great but

How to check F2 also.. i got one more col to check.

Check col F , F2 below
and fill Col D

Case "index": cel = "Index"

Sub [COLOR=#ff0000]FirstPart[/COLOR]()
    Dim cel As Range, ws As Worksheet
    Set ws = Sheets("[I][COLOR=#ff0000]myweeksht[/COLOR][/I]")
    
    For Each cel In ws.Range("D2", ws.Range("D" & ws.Rows.Count).End(xlUp))
        Select Case LCase(Left(cel, 2))
            Case "wp":          cel = "Poster"
            Case 0 To 99:       cel = "Route"
        End Select
    Next cel
End Sub

 

[harky]

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Col F[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Col D (Result)[/TD]
[TD="align: center"]Col E (Result)[/TD]
[/TR]
[TR]
[TD="align: center"]index[/TD]
[TD="align: center"]if Col F is index, it will replace Col D to 16 & Col E to Index[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]Index[/TD]
[/TR]
</tbody>[/TABLE]

Sub FirstPart()
    Dim cel As Range, ws As Worksheet
    Set ws = Sheets("myweeksht")
    
    For Each cel In ws.Range("D2", ws.Range("D" & ws.Rows.Count).End(xlUp))
        Select Case LCase(Left(cel, 2))
            Case "wp":          cel = "Poster"
            Case 0 To 99:       cel = "Route"
        End Select
    Next cel
End Sub

[COLOR=#ff0000] For Each cel In ws.Range("F2", ws.Range("F" & ws.Rows.Count).End(xlUp))[/COLOR]
[COLOR=#ff0000]        Select Case LCase(Right(cel, 2))[/COLOR]
[COLOR=#ff0000]            Case "index":       cel = "Index"[/COLOR]
[COLOR=#ff0000]        End Select[/COLOR]
[COLOR=#ff0000]    Next cel[/COLOR]

 

[Yongle]

How about

    For Each cel In ws.Range("F2", ws.Range("F" & ws.Rows.Count).End(xlUp))
        If cel = "index" Then cel.Offset(, -2).Resize(, 2) = Array(16, "Index")
    Next cel

 

[harky]

Thanks! it work great

How about

    For Each cel In ws.Range("F2", ws.Range("F" & ws.Rows.Count).End(xlUp))
        If cel = "index" Then cel.Offset(, -2).Resize(, 2) = Array(16, "Index")
    Next cel

 

[harky]

Thank you so much...
eh

i need one more help :D

If Col C is Poster, Delete Col D & Col E (not delete the row), just want to remove the text inside.

How about

    For Each cel In ws.Range("F2", ws.Range("F" & ws.Rows.Count).End(xlUp))
        If cel = "index" Then cel.Offset(, -2).Resize(, 2) = Array(16, "Index")
    Next cel

 

[harky]

Sorry should be



If Col C is Poster, ClearContent Col E (not delete the row), just want to remove the text inside.

 

[Yongle]

if D and E (per post#8)

   For Each cel In ws.Range("C2", ws.Range("C" & ws.Rows.Count).End(xlUp))
        If cel = "Poster" Then cel.Offset(, 1).Resize(, 2).ClearContents
    Next cel

if D only (per post#9)

   For Each cel In ws.Range("C2", ws.Range("C" & ws.Rows.Count).End(xlUp))
        If cel = "Poster" Then cel.Offset(, 1).ClearContents
    Next cel