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