Substitute Range

billandrew · Apr 3, 2018

Good evening

Trying to utilize substitute in VBA. Below is small example of my data. Column B is the substituted data.

Substitute

TD for TR
SP for SK
DQ for DW

Thank You

TR01	TD01
TR02	TD02
SK01	SP01
SK02	SP02
DW01	DQ01
DW01	DQ02

alansidman · Apr 3, 2018

Code:

Option Explicit


Sub subst()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 1 To lr
        If Left(Range("A" & i), 2) = "TR" Then
            Range("A" & i) = "TD" & Right(Range("A" & i), 2)
        ElseIf Left(Range("A" & i), 2) = "SK" Then
            Range("A" & i) = "SP" & Right(Range("A" & i), 2)
        ElseIf Left(Range("A" & i), 2) = "DW" Then
            Range("A" & i) = "DQ" & Right(Range("A" & i), 2)
        End If
    Next i
    Application.ScreenUpdating = True


End Sub

TotallyConfused · Apr 4, 2018

Hello Billandrew

The VBA code written by Alansidman (post # 2) will work okay except for one problem. From your post, I assume you want the substituted codes placed in column B. The way Alan wrote the code, VBA would place the substituted codes in column A, which will destroy the original version.

I took the liberty of rewriting his code so that it won’t overwrite column A. I also added a feature to specify Sheet1, which you may change to suit your workbook. If you desire to use his code, then all you need to do is change the line where he said, Range(“A” & i) = “TD” & Right(Range(“A” & i),2) to read as follows: Range(“B” & i) =“TD” & Right(Range(“A” & i),2)
Do the same thing for the similar lines specifying “SP” and “DW”.
Here is my version.

Code:

Sub subst()

      Dim i As Long, lr As Long

      Dim Ws As Worksheet
      Set Ws = ThisWorkbook.Sheets("Sheet1")
      Application.ScreenUpdating = False
      Ws.Activate
      With Ws
         lr = .Range("A" & Rows.Count).End(xlUp).Row
         For i = 1 To lr
            If Left(.Range("A" & i), 2) = "TR" Then
              .Range("B" & i) = "TD" & Right(.Range("A" & i), 2)
            ElseIf Left(Range("A" & i), 2) = "SK" Then
              .Range("B" & i) = "SP" & Right(.Range("A" & i), 2)
            ElseIf Left(Range("A" & i), 2) = "DW" Then
              .Range("B" & i) = "DQ" & Right(.Range("A" & i), 2)
            End If
         Next i
      End With
      Application.ScreenUpdating = True

  End Sub

Another option that will accomplish the same result if you decide not to use VBA, is to place the following Excel formula in column B and then copy it down the number of rows you need.

=IF(LEFT(A1,2)="TR",SUBSTITUTE(A1,"TR","TD"),IF(LEFT(A1,2)="SK",SUBSTITUTE(A1,"SK","SP"),IF(LEFT(A1,2)="DW",SUBSTITUTE(A1,"DW","DQ"))))

I hope this works for you.
TotallyConfused

billandrew · Apr 4, 2018

Thank you very much

Substitute Range

billandrew

Well-known Member

Excel Facts

alansidman

Well-known Member

TotallyConfused

Board Regular

billandrew

Well-known Member

Similar threads

Forum statistics

Share this page

Substitute Range

billandrew

Well-known Member

Excel Facts

alansidman

Well-known Member

TotallyConfused

Board Regular

billandrew

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock