Hi Guys,
I have a spreadsheet which has some unnecessary characters added onto the end of some records which I need to remove. I am essentially trying to ensure that if a Cell in column A begins with (or contains) "PR" that it only returns the first 12 characters. Other records in column A may remain longer than 12 characters if they don't begin with "PR".
I have written the below code based off of a number of different google searches and some of my very limited knowledge of VBA and it is successfully returning only 12 characters, but for everything, not just for records that begin with PR.
Is someone able work out where I've gone wrong and assist?
Sub Macro1()
Dim x As Long
Dim Cell As Range
Dim SourceRange As Range
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B:B").Select
x = Selection.Find(What:="PR", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Set SourceRange = Range(Sheets("Sheet1").Range("B1"), Selection.End(xlDown))
For Each Cell In SourceRange
If x = True Then
Range("A" & Cell.Row).Value = Left$(Cell.Value, 12)
Else
Range("A" & Cell.Row).Value = Cell.Value
End If
Next
'Columns("B:B").Select
'Selection.Delete Shift:=xlToLeft
End Sub
Bookmarks