PDA

View Full Version : [SOLVED] Need VBA to arrange data Cell adress data please!!



estatefinds
08-04-2017, 05:01 PM
Hello, I have data in a column AO and need use of a VBA program to arrange the cell adress in stead of looking like Q369 haVe it look like 369-Q.
Any help in this problem is greatly appreciated!!!
Thank you very much in advance!

A file is attached, I need it to be where the data is NOT copiEd to another column, but have the change occur in the column AO.

Paul_Hossler
08-04-2017, 06:37 PM
Not elegant, but simple




Option Explicit

Sub SplitAddress()
Dim c As Range
Dim i As Long

Application.ScreenUpdating = False

On Error GoTo NiceExit
For Each c In ActiveSheet.Range("AO:AO").SpecialCells(xlCellTypeConstants).Cells
For i = 1 To Len(c.Value)
If Mid(c.Value, i, 1) Like "[0-9]" Then
Exit For
End If
Next I

c.Value = Right(c.Value, Len(c.Value) - i + 1) & "-" & Left(c.Value, i - 1)
Next

NiceExit:
Application.ScreenUpdating = True
End Sub

estatefinds
08-04-2017, 06:54 PM
Wow it worked Great thank you!!!!
Very much appreciated!!!
Sincerely,
Dennis