Alternative to Loops

jameskilburn · Jun 27th 2015

I have two worksheets.

Sheet one is a customer address listing, imported from an accounts package. The fields are not consistent. There can be several words per cell and several cells per customer (each customer has their own row). There are blank fields within the data (rows and columns).

Sheet two is a list of towns and cities that I need to identify within the customer name and address listing. The data is categorised so there can be several towns listed on each row, with only one town listed per cell. There are blank fields within the data. There are different numbers of towns per row.

I have written a VBA loop to compare each cell but it is very slow.

I understand that using Excel Special Cells is the way to go but I have looked at them and don’t see how it is done.

Extract from current code:

Code

For Each cell In rng.Cells 'all cells on sheet 1
     For Each town In townrange.Cells 'the range pointing to the list of towns on sheet 2 e.g. a1:p260
           If InStr(UCase(cell.Value), UCase(town.Value)) then cells(cell.row,1).value = town.value
     Next town
Next cell

Many thanks!

cytop · Jun 27th 2015

Re: Alternative to Loops

I think a representative sample of your data would be helpful...

[sw]*[/sw]

jameskilburn · Jun 27th 2015

Re: Alternative to Loops

I have attached a sample of data with made up addresses instead of real data but the format is the same.

Thanks!

Anonymous · Jun 28th 2015

Re: Alternative to Loops

In your code, you could speed it up by exiting the town loop once a town is found for a address cell. You could then speed it up by going through the addresses a row at a time and exiting as soon as a row;s town is found. There will always be the problem of matching the town of York in the address row that has New York.
Below is an alternate approach that may be faster or may not.

Code

Sub MatchCities()
    Dim c As Range, i As Long, iFirst As Long, rAddresses As Range, rTowns As Range, _
        ws As Worksheet
    Set ws = Sheets("Addresses")
    With ws
        .Columns(1).Clear
        Set rAddresses = .Cells.SpecialCells(xlCellTypeConstants, 23)
    End With
    Set rTowns = Sheets("Towns").Cells.SpecialCells(xlCellTypeConstants, 23)
    On Error Resume Next 'so things continue if a town is not found
    With rAddresses
        For Each c In rTowns
            i = .Find(What:=c, LookIn:=xlValues, LookAt:=xlPart).Row
            If Err.Number = 0 Then
                iFirst = i
                Do
                    ws.Cells(i, 1) = c
                    i = .Find(c).Row
                Loop While i <> iFirst
            Else
                Err.Clear
            End If
        Next c
    End With
End Sub

Display More

jameskilburn · Jun 28th 2015

Re: Alternative to Loops

Thanks,

I will give it a go!

**KjBox** · Jun 29th 2015

Re: Alternative to Loops

What are you ultimately trying to find/do?

Do you need to give a category to each customer? If so in your sample file if the company name is not in column B will it be in column A?

If the code you wrote is slow it must be because of the size of data you are searching, loading all data into VBA arrays and searching through the arrays would be much faster.

Alternative to Loops

Participate now!

Share