CODEWITHSUNDEEP
excelvba
Tushar
Tushar

Reputation: 3623

Comparing records from two columns with unequal lengths

I am doing validation of two columns(with unequal lengths) present on two different sheets of excel.

The first sheet name is 'Task'. Column 'A' of the 'Task' sheet has about 200 City Names.

The second sheet is 'Cities'. Column 'A' of the 'Cities' sheet has about 8000+ City Names.

Now I need to do validation such that the name of City in Column A of 'Task' sheet should be

  1. Either one among the City Names specified in column 'A' of sheet 'Cities'

  2. Or It can have multiple entries separated with semicolon; after separating all cities based on semicolon each city names shall match with that one in column D of 'Cities' sheet.

  3. If not among both the above cases then it should be 'All'

The cells in 'Task' Sheet from which City names do not match are to be turned in Red background

My Code is as follows: (am just giving required part of code)

Dim CityString As String
Dim CityArray() As String

'Get the last row
'Dim lastRow As Integer
LastRow = ActiveSheet.UsedRange.Rows.Count


Dim c As Range
Dim d As Range
Dim e As Variant

'Turn screen updating off to speed up macro code.
'User won't be able to see what the macro is doing, but it will run faster.
Application.ScreenUpdating = False

For Each c In Worksheets("Task").Range("A2:A" & LastRow).Cells
CityString = c
CityArray() = Split(CityString, ";")
For Each e In CityArray()
e = Trim(e)
    For Each d In Worksheets("Cities").Range("A2:A" & LastRow).Cells
        c.Interior.Color = vbRed
                    
        If (UCase(e) = UCase(d) Or c = "All") Then
        c.Interior.Color = vbWhite
        Exit For
        End If
    Next
    If c.Interior.Color = vbRed Then
    Exit For
    End If
Next
Next

Now, the above code is valid only when both the sheets (Sheet1 - 'Task' and Sheet2 - 'Cities' have equal number of records. If Sheet2 - 'Cities' has more records than the 'Task' Sheet, the above code do not work.

For Example: New York is at 'A55' Cell of 'Task' sheet. It's also there in 'Cities' Sheet at 'A41'. My code validates the cell correctly.

For Example: 'A53' of 'Task' sheet I have 'Tokyo' and 'Task' Sheet has only 200 records, where as 'Tokyo' is present at 'A988' of 'Cities' sheet, which has some 8000+ records, then my code does not validate this cell correctly.

Can it be done in better way ?

Upvotes: 0

Views: 1626

Answers (1)

basodre
basodre

Reputation: 5770

It's not very efficient to loop through a list of values searching for a match. The longer each of the lists becomes, the longer it will take to run the macro. Instead, use the built-in FIND method to search for values.

I've updated the code to show the FIND method in action. Check it out and let me know if this makes sense/works.

(SIDE NOTE): I left a comment above detailing why your initial code wasn't working correctly. You needed a second variable to reference the last row of the cities sheet.

Dim CityString As String
Dim CityArray() As String

'Get the last row
'Dim lastRow As Integer
LastRow = Sheets("Task").UsedRange.Rows.Count
nLastRowSheet2 = Sheets("Cities").UsedRange.Rows.Count

Dim c As Range
Dim d As Range
Dim e As Variant

'Turn screen updating off to speed up macro code.
'User won't be able to see what the macro is doing, but it will run faster.
Application.ScreenUpdating = False

For Each c In Worksheets("Task").Range("A2:A" & LastRow)
    CityString = c
    CityArray() = Split(CityString, ";")
    For Each e In CityArray()
        e = Trim(e)

        Dim rngFnder As Range
        On Error Resume Next

            Set rngFnder = Sheets("Cities").Range("A2:A" & nLastRowSheet2).Find(e)

            If rngFnder Is Nothing Then
                c.Interior.Color = vbRed
            End If

        On Error GoTo 0
    Next
Next

Upvotes: 1

Related Questions