Alternative to Loops

  • 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!

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

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

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!