Running a string splitting function in a loop

Question

I have an excel sheet, with a column filled with about 10 complete standard addresses with intermittent blank (nulls)

All addresses are in the same format:

123 Street Name, Suburb QLD 4123

What i'm trying to do is create an automatic splitter where upon a BUtton7_Click the macro loops through the column and splits up the street name with number, suburb, state code and post code into separate columns. Thanks to a contributor here I got a good core function working which separates the address given as a static value.

Sub Button7_Click()
Dim strTest     As String

Dim arr1
Dim arr2

Dim StreetAddress As String
Dim Postcode As String
Dim StateCode As String
Dim SubUrb As String


strTest = "62 Norma Rd, Myaree WA 6154"

arr1 = Split(strTest, ",")
arr2 = Split(Trim(arr1(1)), Space(1))

StreetAddress = arr1(0)
Postcode = arr2(2)
StateCode = arr2(1)
SubUrb = arr2(0)

Range("E3").Value = arr1(0)
Range("F3").Value = arr2(0)
Range("G3").Value = arr2(1)
Range("H3").Value = arr2(2)


End Sub

The issue I'm facing is getting that to run...

1. In a loop
2. Independent of the column size (However I do know I need to use something like "For LngRow = 2 To Wksht.Range("D" & Wksht.Rows.Count).End(xlUp).Row"
3. Ignoring Null values (Need to use if Len(address_string) > 0 Then exit)
4. Using ubound for double name suburbs.

I figure the best first step is to build the loop, then implement case validation, then column count and lastly ubound.

However I tried using a loop function used in my last question but it didn't work and I have never used ubound before, can someone help me?

Answer 1

Sub Button7_Click()
    Dim strTest     As String

    Dim arr1
    Dim arr2

    Dim StreetAddress As String
    Dim Postcode As String
    Dim StateCode As String
    Dim Suburb As String

    Dim LngRow As Long
    Dim i As Integer

    With ActiveSheet
        For LngRow = 2 To .Range("D" & .Rows.Count).End(xlUp).Row
            strTest = .Cells(LngRow, 4).Value

            If Len(Trim(strTest)) > 0 Then

                arr1 = Split(strTest, ",")
                If UBound(arr1) - LBound(arr1) < 1 Then
                    MsgBox "No comma in address on row " & LngRow & " '" & strTest & "'"
                Else
                    arr2 = Split(Trim(arr1(1)), Space(1))
                    If UBound(arr2) - LBound(arr2) < 2 Then
                        MsgBox "Only " & UBound(arr2) - LBound(arr2) & " spaces after the comma in address on row " & LngRow & " '" & strTest & "'"
                    Else

                        StreetAddress = arr1(0)
                        Postcode = arr2(UBound(arr2))
                        StateCode = arr2(UBound(arr2) - 1)
                        Suburb = ""
                        For i = LBound(arr2) To UBound(arr2) - 2
                            Suburb = Suburb & " " & arr2(i)
                        Next
                        .Cells(LngRow, 5).Value = Trim(StreetAddress)
                        .Cells(LngRow, 6).Value = Trim(Suburb)
                        .Cells(LngRow, 7).Value = Trim(StateCode)
                        .Cells(LngRow, 8).Value = Trim(Postcode)
                    End If
                End If
            End If
        Next
    End With
End Sub

Answer 2

Alternatively, you can use Range.TextToColumns method to parse a column of cells that contain text into several columns. Here I'm assuming your address data in column A and the suburb is only a single word:

Sub AddressSpliter()
Dim LastRow&, iRow&
On Error Resume Next
Application.DisplayAlerts = False
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

For iRow = 2 To LastRow
    Cells(iRow, 1).TextToColumns Destination:=Cells(iRow, 2), DataType:=xlDelimited, Comma:=True
    ResetText2Columns
    Cells(iRow, 3).TextToColumns Destination:=Cells(iRow, 3), DataType:=xlDelimited, Space:=True
    ResetText2Columns
Next
Application.DisplayAlerts = True
End Sub

Sub ResetText2Columns()
On Error Resume Next
Cells(2, 1).TextToColumns Destination:=Cells(2, 1), DataType:=xlDelimited, ConsecutiveDelimiter:=False, _
            Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar:=False
End Sub