Problem with comparing sheets using an array

[JEngles]

Hi all, I'm trying to create a macro that compares data from 2 tabs and sees if there is any matches. The problem is that the data is slightly different. Sheet1 contains 2800 of application names (e.g "Microsoft Office Visio Viewer 2003 (English)" and I have to try and find matches in Sheet2 where the answer might be "Office Visio Viewer 2003 (Engl"

I've tried various instr and find commands, but I just can't find something accurate enough. In the end I thought it just maybe best to put the individual words into an array and they compare one by one.

Sub CompareApps()

Dim StringArray() As String
Dim CompareArray() As String

Number = 2
Sheets("Sheet1").Select
    For ACount = 2 To 1616
        StringToCompare = UCase(Sheets("Sheet2").Range("C" & ACount).Value)
        CountWords = Len(StringToCompare) - Len(Replace(StringToCompare, " ", "")) + 1
        If CountWords > 0 Then
            ReDim StringArray(1 To CountWords)
            StartPoint = 1
            For Count = 1 To CountWords
                EndPoint = (InStr(StartPoint, StringToCompare, " ", 1)) - 1
                If EndPoint < 1 Then
                    StringArray(Count) = UCase(Mid(StringToCompare, StartPoint, (Len(StringToCompare) - (StartPoint - 1))))
                Else
                    StringArray(Count) = UCase(Mid(StringToCompare, StartPoint, (EndPoint - (StartPoint - 1))))
                End If
                StartPoint = EndPoint + 2
            Next Count
        End If
        
        BCount = 2
        Do While Range("A" & BCount) <> ""
            StringToCompare = UCase(Range("A" & BCount).Value)
            CountWords = Len(StringToCompare) - Len(Replace(StringToCompare, " ", "")) + 1
            If CountWords > 0 Then
                ReDim CompareArray(1 To CountWords)
                StartPoint = 1
                For Count = 1 To CountWords
                    EndPoint = (InStr(StartPoint, StringToCompare, " ", 1)) - 1
                    If EndPoint < 1 Then
                        CompareArray(Count) = UCase(Mid(StringToCompare, StartPoint, (Len(StringToCompare) - (StartPoint - 1))))
                    Else
                        CompareArray(Count) = UCase(Mid(StringToCompare, StartPoint, (EndPoint - (StartPoint - 1))))
                    End If
                    StartPoint = EndPoint + 2
                Next Count
            End If
            
            HowMany = 0
            For CCount = 1 To UBound(StringArray)
                For DCount = 1 To UBound(CompareArray)
                    If StringArray(CCount) = CompareArray(DCount) Then
                       HowMany = HowMany + 1
                    End If
                Next DCount
            Next CCount
            Percentage = HowMany / UBound(CompareArray)
            BCount = BCount + 1
        Loop
    Next ACount
End Sub

Now for starters, I'm not even sure if it's any good. But I'm also getting a error when it starts to do the compare. Basically what's happening is that UBound(CompareArray) = 1, so it's doing a For 1 to 1 and then at the end it's Next adds another and erros due to the array not being that big.

Thanks heaps in advance, hope you don't get too bored reading this.

Cheers,
JEngles

 

[SkipVought]

hi,

WHERE in your code do you get an error?

So do you need help only with your code or with your process as well?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JEngles]

Hi Skip,

The problem is here:

For CCount = 1 To UBound(StringArray)
  For DCount = 1 To UBound(CompareArray)
    If StringArray(CCount) = CompareArray(DCount) Then
      HowMany = HowMany + 1
    End If
  Next DCount
Next CCount

For reasons I'm not sure about, it's adding an extra loop in the for counts? e.g UBound(StringArray) should be 3, but it's looping to 4??

But I guess if you have a better process, that would also be mightly helpful

Cheers,
JEngles

 

[SkipVought]

if the ubound function returns 4, then there ARE 4 elements!

That is not a run error. May be a LOGIC error. Use the Watch Window to examine the elements in your array. faq707-4594

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JEngles]

Thanks Skip, but UBound is actualy equal to 3. It's a strange one when I look at it in the Watch Window, it jumps to 4 and errors if I don't put an On Error Resume Next in, but if I do put that in it jumps to 4, and then a line later is jumps back to 3??

Anyway, I've got this working now by simple adding a On Error Resume Next before the array compare.

Thanks for your help.

 

[combo]

Your formula for CountWords returns number of spaces + 1. You get 1 for empty or single word cell. Text can contain leading, trailing or doubled spaces. How to proceed with it?
I'd use Split function to split and test words, Ubound to count words, seems to be a bit simpler.

combo