VBA: Replace X with X @ Col X

harky

Active Member
Joined
Apr 8, 2010
Messages
405
Office Version
  1. 2021
  2. 2019
Platform
  1. Windows
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'

Col DCol D (Result)
WP1find WP @ Col D & replace with 'PosterPoster
WP2''Poster
5Any numbers found @ Col D will replace with 'Route'Route
1Route
3Route

<tbody>
</tbody>


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


Col DCol E
1Index
find Index @ Col E & replace with with 'index' at Col D​
5Indexfind Index @ Col E & replace with with 'index' at Col D
6Index
find Index @ Col E & replace with with 'index' at Col D​

<tbody>
</tbody>



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

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
try xlpart not xlwhole as that refers to the whole cell
 
Last edited:
Upvote 0
Code:
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
 
Last edited:
Upvote 0
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"


Code:
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
 
Upvote 0
Col FCol D (Result)Col E (Result)
indexif Col F is index, it will replace Col D to 16 & Col E to Index16Index

<tbody>
</tbody>



Code:
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]
 
Last edited:
Upvote 0
How about
Code:
    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
 
Last edited:
Upvote 0
Thanks! it work great

How about
Code:
    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
 
Upvote 0
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
Code:
    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
 
Upvote 0
Sorry should be



If Col C is Poster, ClearContent Col E (not delete the row), just want to remove the text inside.
 
Upvote 0
if D and E (per post#8)
Code:
   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)
Code:
   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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top