VBA move character to next column

jaik22

Board Regular
Joined
Sep 23, 2016
Messages
102
I want to write a code that If column A contains REG then move it over to column B and remove it from A.
For example

before
a1 b1
tsf-REG

after

a1 b1
tsf REG


How can I write code for this kind of purpose?
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,076
Office Version
2019
Platform
Windows
Give this a try

Code:
Option Explicit


Sub RegX()
    Dim i As Long, lr As Long, x As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        If InStr(1, Range("A" & i), "REG") > 0 Then
            x = InStr(1, Range("A" & i), "REG")
            Range("A" & i) = Left(Range("A" & i), Len(Range("A" & i)) - 5)
            Range("B" & i) = "REG"
        End If
    Next i


End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,511
Office Version
2010
Platform
Windows
Give this a try
Code:
Option Explicit

Sub RegX()
    Dim i As Long, lr As Long, x As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        If InStr(1, Range("A" & i), "REG") > 0 Then
            x = InStr(1, Range("A" & i), "REG")
            Range("A" & i) = Left(Range("A" & i), Len(Range("A" & i)) - 5)
            Range("B" & i) = "REG"
        End If
    Next i
End Sub
Here is another way to write this macro...
Code:
[table="width: 500"]
[tr]
	[td]Sub RegX2()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B1:B" & LastRow) = Evaluate(Replace("IF(ISNUMBER(FIND(""-"",A1:A#)),REPLACE(A1:A#,1,FIND(""-"",A1:A#),""""),IF(B1:B#="""","""",B1:B#))", "#", LastRow))
  Columns("A").Replace "-*", "", xlPart
End Sub[/td]
[/tr]
[/table]
 

Forum statistics

Threads
1,082,575
Messages
5,366,415
Members
400,888
Latest member
Cdim7

Some videos you may like

This Week's Hot Topics

Top