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...

In a loop
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"
Ignoring Null values (Need to use if Len(address_string) > 0 Then exit)
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?

YowE3K · Accepted Answer · 2016-08-18 04:09:56Z

2

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

edited Aug 18, 2016 at 4:09

answered Aug 18, 2016 at 2:13

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Tummy Tum Over a year ago

Wow that looks good! Thank you. I'm getting a subscript out of range error though, seems to be a common error with arrays within loops. Is it because the number of elements specified is wrong? The error is specifically coming from arr2 = Split(Trim(arr1(1)), Space(1))

YowE3K Over a year ago

Does one of your (non-blank) addresses have no comma? The code is assuming that there is a comma, and therefore splitting the original into the first part (arr1(0)) and the second part (arr1(1), and it assumes the second part has at least 2 spaces (creating arr2(0), arr2(1), arr2(2) and possibly arr2(3), etc).

YowE3K Over a year ago

I'll update the answer to bring up a msgbox if an error occurs. Done.

Tummy Tum Over a year ago

Nope every address has a comma, the address refiner pulls the address from Google which has that same standard output.

Tummy Tum Over a year ago

It says there is no comma on row 2 and 3, but there is. 12 Smith St, Chatswood NSW 2067 62 Norma Rd, Myaree WA 6154

|

Anastasiya-Romanova 秀 · Accepted Answer · 2016-08-18 03:51:46Z

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

Collectives™ on Stack Overflow

Running a string splitting function in a loop

2 Answers 2

12 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related