Split Address Column

I need to take an Excel Database that has an Address column setup like: "Physical Address, Mailing Address, Suit#; City; ST; Zip" into columns split by the semi-colon.

Unfortunately I have data inside the Address column that has ASCII character references that isn't allowing me to just use "Text to Columns" so I developed the following code, but it doesn't do what I am wanting it to do. The range I am splitting is in Column B.

Sub SplitAddress()
    Dim txt As String
    Dim i As Integer
    Dim j As Integer
    Dim Address As Variant
    Dim Rng As Range
    Dim Row As Range
    Dim LastRow As Integer

    txt = ActiveCell.Value
    Address = Split(txt, "; ")

    LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    Rng = Range("B3:B" & LastRow)

    j = 1

    For Each Row In Rng.Rows
        For i = 0 To UBound(Address)
            Cells(3, j + 1).Value = Address(i)
        Next i
    Next Row
End Sub

Answers


Perhaps:

Sub SplitAddress()
    Dim txt As String
    Dim i As Integer
    Dim j As Integer
    Dim Address As Variant
    Dim Rng As Range
    Dim R As Range
    Dim LastRow As Integer
    LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    Set Rng = Range("B3:B" & LastRow)
    For Each R In Rng
        txt = R.Value
        Address = Split(txt, "; ")
        j = R.Row
        For i = 0 To UBound(Address)
            Cells(j, i + 3).Value = Address(i)
        Next i
    Next R
End Sub

EDIT#1:

Better to make i, j, LastRow Long rather than integer:

Sub SplitAddress()
    Dim txt As String
    Dim i As Long
    Dim j As Long
    Dim Address As Variant
    Dim Rng As Range
    Dim R As Range
    Dim LastRow As Long
    LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    Set Rng = Range("B3:B" & LastRow)
    For Each R In Rng
        txt = R.Value
        Address = Split(txt, "; ")
        j = R.Row
        For i = 0 To UBound(Address)
            Cells(j, i + 3).Value = Address(i)
        Next i
    Next R
End Sub

EDIT#2:

This version moves the result to the left and thus over-writes column B:

Sub SplitAddress()
    ' version #3 - overwrites column B
    Dim txt As String
    Dim i As Long
    Dim j As Long
    Dim Address As Variant
    Dim Rng As Range
    Dim R As Range
    Dim LastRow As Integer
    LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    Set Rng = Range("B3:B" & LastRow)
    For Each R In Rng
        txt = R.Value
        Address = Split(txt, "; ")
        j = R.Row
        For i = 0 To UBound(Address)
            Cells(j, i + 2).Value = Address(i)
        Next i
    Next R
End Sub

Need Your Help

Finding Bugs In NodeJS Website

javascript node.js debugging mongoose

I'm coming to the end of the development of my project and am wanting to finalise it by finding any remaining bugs, the problem is I don't want to manually run through my website over and over. I was