What's wrong with this code???

[Twain]

To give you some background on this I am trying to compare values fro
two different workbooks, then if the values match copy over that ro
from Workbook Z to workbook A. I can't seem to spot what I am doin
wrong here.... All help is greatly greatly appreciated. Thanks.


Code
-------------------

Private Sub cmdCompare_Click()
Dim strTable(1000, 24) As String
Dim strTableResult(1000, 24) As String
Dim strTimeBox As String
Dim intRow As Long
Dim intIndex As Long
Dim intIndexResult As Long
Dim blnFounded As Boolean


'Alert the user that this will take some time
strTimeBox = MsgBox("This process can take up to two minutes. Do not touch your mouse or keyboard during this time.", vbInformation, "Please be Patient...")

'Load table with search values
Sheets(1).Select
intRow = 2 'start reading on row 2, as row 1 is the heading
intIndex = 0
Do While ActiveSheet.Cells(intRow, 1).Value <> ""
strTable(intIndex, 0) = ActiveSheet.Cells(intRow, 1)
intIndex = intIndex + 1
intRow = intRow + 1
Loop

'Search list
Workbooks.Open "\\Mddp-oa-020\MddUsrCrtI\User Certification Listing.xls"
intIndex = 0
intIndexResult = 0
Do While strTable(intIndex, 0) <> ""
intRow = 2
blnFounded = False
Do While ActiveSheet.Cells(intRow, 1).Value <> ""
If ActiveSheet.Cells(intRow, 1).Value = strTable(intIndex, 0) Then
blnFounded = True
Exit Do
End If
intRow = intRow + 1
Loop

If blnFounded = True Then
'Load result in result-table
strTableResult(intIndexResult, 0) = ActiveSheet.Cells(intIndex, 1)
strTableResult(intIndexResult, 1) = ActiveSheet.Cells(intIndex, 2)
strTableResult(intIndexResult, 2) = ActiveSheet.Cells(intIndex, 3)
strTableResult(intIndexResult, 3) = ActiveSheet.Cells(intIndex, 4)
strTableResult(intIndexResult, 4) = ActiveSheet.Cells(intIndex, 5)
strTableResult(intIndexResult, 5) = ActiveSheet.Cells(intIndex, 6)
strTableResult(intIndexResult, 6) = ActiveSheet.Cells(intIndex, 6)
strTableResult(intIndexResult, 7) = ActiveSheet.Cells(intIndex, 7)
strTableResult(intIndexResult, 8) = ActiveSheet.Cells(intIndex, 8)
strTableResult(intIndexResult, 9) = ActiveSheet.Cells(intIndex, 9)
strTableResult(intIndexResult, 10) = ActiveSheet.Cells(intIndex, 10)
strTableResult(intIndexResult, 11) = ActiveSheet.Cells(intIndex, 11)
strTableResult(intIndexResult, 12) = ActiveSheet.Cells(intIndex, 12)
strTableResult(intIndexResult, 13) = ActiveSheet.Cells(intIndex, 13)
strTableResult(intIndexResult, 14) = ActiveSheet.Cells(intIndex, 14)
strTableResult(intIndexResult, 15) = ActiveSheet.Cells(intIndex, 15)
strTableResult(intIndexResult, 16) = ActiveSheet.Cells(intIndex, 16)
strTableResult(intIndexResult, 17) = ActiveSheet.Cells(intIndex, 17)
strTableResult(intIndexResult, 18) = ActiveSheet.Cells(intIndex, 18)
strTableResult(intIndexResult, 19) = ActiveSheet.Cells(intIndex, 19)
strTableResult(intIndexResult, 20) = ActiveSheet.Cells(intIndex, 20)
strTableResult(intIndexResult, 21) = ActiveSheet.Cells(intIndex, 21)
strTableResult(intIndexResult, 22) = ActiveSheet.Cells(intIndex, 22)
strTableResult(intIndexResult, 23) = ActiveSheet.Cells(intIndex, 23)
strTableResult(intIndexResult, 24) = ActiveSheet.Cells(intIndex, 24)
intIndexResult = intIndexResult + 1
End If
intIndex = intIndex + 1
Loop
ActiveWorkbook.Close

'Writing result table in sheet results
Sheets(1).Select
intIndexResult = 0
intRow = 3
Do While strTableResult(intIndexResult, 0) <> ""
ActiveSheet.Cells(intRow, 1).Value = strTableResult(intIndexResult, 0)
ActiveSheet.Cells(intRow, 2).Value = strTableResult(intIndexResult, 1)
ActiveSheet.Cells(intRow, 3).Value = strTableResult(intIndexResult, 2)
ActiveSheet.Cells(intRow, 4).Value = strTableResult(intIndexResult, 3)
ActiveSheet.Cells(intRow, 5).Value = strTableResult(intIndexResult, 4)
ActiveSheet.Cells(intRow, 6).Value = strTableResult(intIndexResult, 5)
ActiveSheet.Cells(intRow, 7).Value = strTableResult(intIndexResult, 6)
ActiveSheet.Cells(intRow, 8).Value = strTableResult(intIndexResult, 7)
ActiveSheet.Cells(intRow, 9).Value = strTableResult(intIndexResult, 8)
ActiveSheet.Cells(intRow, 10).Value = strTableResult(intIndexResult, 9)
ActiveSheet.Cells(intRow, 11).Value = strTableResult(intIndexResult, 10)
ActiveSheet.Cells(intRow, 12).Value = strTableResult(intIndexResult, 11)
ActiveSheet.Cells(intRow, 13).Value = strTableResult(intIndexResult, 12)
ActiveSheet.Cells(intRow, 14).Value = strTableResult(intIndexResult, 13)
ActiveSheet.Cells(intRow, 15).Value = strTableResult(intIndexResult, 14)
ActiveSheet.Cells(intRow, 16).Value = strTableResult(intIndexResult, 15)
ActiveSheet.Cells(intRow, 17).Value = strTableResult(intIndexResult, 16)
ActiveSheet.Cells(intRow, 18).Value = strTableResult(intIndexResult, 17)
ActiveSheet.Cells(intRow, 19).Value = strTableResult(intIndexResult, 18)
ActiveSheet.Cells(intRow, 20).Value = strTableResult(intIndexResult, 19)
ActiveSheet.Cells(intRow, 21).Value = strTableResult(intIndexResult, 20)
ActiveSheet.Cells(intRow, 22).Value = strTableResult(intIndexResult, 21)
ActiveSheet.Cells(intRow, 23).Value = strTableResult(intIndexResult, 22)
ActiveSheet.Cells(intRow, 24).Value = strTableResult(intIndexResult, 23)
intIndexResult = intIndexResult + 1
intRow = intRow + 1
Loop
End Sub

 

[Greg Wilson]

********* Snippet **********
Workbooks.Open "\\Mddp-oa-020\MddUsrCrtI\User Certification Listing.xls"
intIndex = 0
intIndexResult = 0
Do While strTable(intIndex, 0) <> ""
intRow = 2
blnFounded = False
Do While ActiveSheet.Cells(intRow, 1).Value <> ""
If ActiveSheet.Cells(intRow, 1).Value = strTable(intIndex, 0) Then
blnFounded = True
Exit Do
End If
intRow = intRow + 1
Loop

If blnFounded = True Then
'Load result in result-table
strTableResult(intIndexResult, 0) = ActiveSheet.Cells(intIndex, 1)

******* End snippet *********

If my assessment is correct, the above code errors on the last line because
the intIndex variable equals zero at the start of the loop - i.e. the
statement "Cells(intIndex, 1)" equates with Cells(0, 1). The code goes on to
increment the intIndex variable after the first run of the loop. Apparently,
you need to add 1 to intIndex at the start.

I have not deciphered the entire macro, but I suspect there are more
problems. I don't understand why you need to use an array since you can
compare cell ranges in different workbooks directly. In other words, why not
establish range variables for both wbs and compare directly?

The following simplified code was successful in comparing cells in ranges of
two different wbs and coloured the cells yellow in the first wb where
duplicates were found:

Sub xyz()
Dim wb1 As Workbook, wb2 As Workbook
Dim pth As String, fn As String
Dim rng1 As Range, rng2 As Range
Dim c As Range, cc As Range

pth = ThisWorkbook.Path & "\"
fn = pth & "July31Test.xls"
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open(fn)
Set rng1 = wb1.Sheets("Sheet1").Range("A1:B10")
Set rng2 = wb2.Sheets("Sheet1").Range("A1:B10")
For Each c In rng1
For Each cc In rng2
If c.Value = cc.Value And Len(c) > 0 Then _
c.Interior.ColorIndex = 6
Next
Next
wb2.Close SaveChanges:=False
End Sub

Regards,
Greg

 

[Tim Williams]

Untested, but together with Greg's code may provide some hints.

Tim


Sub GetMatches()

Dim rngLookup As Range
Dim rngSearch As Range
Dim rngFound As Range
Dim rngCopy As Range


Workbooks.Open "\\Mddp-oa-020\MddUsrCrtI\User Certification
Listing.xls"

Set rngSearch = Range(ActiveSheet.Cells(2, 1), _
ActiveSheet.Cells(2, 1).End(xlDown))

Set rngCopy =
ThisWorkbook.Sheets("Matches").Range("A1000").End(xlUp).Offset(1, 0)

Set rngLookup = ThisWorkbook.Sheets("Lookups").Range("A2")

Do While rngLookup.Value <> ""

Set rngFound = rngSearch.Find(what:=rngLookup.Value,
lookat:=xlWhole)

If Not rngFound Is Nothing Then
rngFound.Resize(1, 24).Copy rngCopy
Set rngCopy = rngCopy.Offset(1, 0)
End If

Set rngLookup = rngLookup.Offset(1, 0)
Loop

End Sub

 

[Twain]

Since I am not very advanced in VBA I have to stick with what I
have/know. At the moment I click the compare button, I get the
hourglass, and nothing ends up happening. What is going on???


Code:
--------------------

Private Sub cmdCompare_Click()
Dim strTable(1000, 25) As String
Dim strTableResult(1000, 25) As String
Dim strTimeBox As String
Dim intRow As Long
Dim intIndex As Long
Dim intIndexResult As Long
Dim blnFounded As Boolean


'Alert the user that this will take some time
strTimeBox = MsgBox("This process can take up to two minutes. Do not touch your mouse or keyboard during this time.", vbInformation, "Please be Patient...")

'Load table with search values
Sheets(1).Select
intRow = 2 'start reading on row 2, as row 1 is the heading
intIndex = 0
Do While ActiveSheet.Cells(intRow, 1).Value <> ""
strTable(intIndex, 0) = ActiveSheet.Cells(intRow, 1)
intIndex = intIndex + 1
intRow = intRow + 1
Loop

'Search list
Workbooks.Open "\\Mddp-oa-020\MddUsrCrtI\User Certification Listing.xls"
intIndex = 0
intIndexResult = 0
Do While strTable(intIndex, 0) <> ""
intRow = 2
blnFounded = False
Do While ActiveSheet.Cells(intRow, 1).Value <> ""
If ActiveSheet.Cells(intRow, 1).Value = strTable(intIndex, 0) Then
blnFound = True
Exit Do
End If
intRow = intRow + 1
Loop

If blnFound = True Then
'Load result in result-table
strTableResult(intIndexResult, 1) = ActiveSheet.Range("A" & CStr(intRow))
strTableResult(intIndexResult, 2) = ActiveSheet.Range("B" & CStr(intRow))
strTableResult(intIndexResult, 3) = ActiveSheet.Range("C" & CStr(intRow))
strTableResult(intIndexResult, 4) = ActiveSheet.Range("D" & CStr(intRow))
strTableResult(intIndexResult, 5) = ActiveSheet.Range("E" & CStr(intRow))
strTableResult(intIndexResult, 6) = ActiveSheet.Range("F" & CStr(intRow))
strTableResult(intIndexResult, 7) = ActiveSheet.Range("G" & CStr(intRow))
strTableResult(intIndexResult, 8) = ActiveSheet.Range("H" & CStr(intRow))
strTableResult(intIndexResult, 9) = ActiveSheet.Range("I" & CStr(intRow))
strTableResult(intIndexResult, 10) = ActiveSheet.Range("J" & CStr(intRow))
strTableResult(intIndexResult, 11) = ActiveSheet.Range("K" & CStr(intRow))
strTableResult(intIndexResult, 12) = ActiveSheet.Range("L" & CStr(intRow))
strTableResult(intIndexResult, 13) = ActiveSheet.Range("M" & CStr(intRow))
strTableResult(intIndexResult, 14) = ActiveSheet.Range("N" & CStr(intRow))
strTableResult(intIndexResult, 15) = ActiveSheet.Range("O" & CStr(intRow))
strTableResult(intIndexResult, 16) = ActiveSheet.Range("P" & CStr(intRow))
strTableResult(intIndexResult, 17) = ActiveSheet.Range("Q" & CStr(intRow))
strTableResult(intIndexResult, 18) = ActiveSheet.Range("R" & CStr(intRow))
strTableResult(intIndexResult, 19) = ActiveSheet.Range("S" & CStr(intRow))
strTableResult(intIndexResult, 20) = ActiveSheet.Range("T" & CStr(intRow))
strTableResult(intIndexResult, 21) = ActiveSheet.Range("U" & CStr(intRow))
strTableResult(intIndexResult, 22) = ActiveSheet.Range("V" & CStr(intRow))
strTableResult(intIndexResult, 23) = ActiveSheet.Range("W" & CStr(intRow))
strTableResult(intIndexResult, 24) = ActiveSheet.Range("X" & CStr(intRow))
strTableResult(intIndexResult, 25) = ActiveSheet.Range("Y" & CStr(intRow))
intIndexResult = intIndexResult + 1
End If
intIndex = intIndex + 1
Loop
ActiveWorkbook.Close

'Writing result table in sheet results
Sheets(1).Select
intIndexResult = 0
intRow = 3
Do While strTableResult(intIndexResult, 0) <> ""
ActiveSheet.Cells(intRow, 1).Value = strTableResult(intIndexResult, 0)
ActiveSheet.Cells(intRow, 2).Value = strTableResult(intIndexResult, 1)
ActiveSheet.Cells(intRow, 3).Value = strTableResult(intIndexResult, 2)
ActiveSheet.Cells(intRow, 4).Value = strTableResult(intIndexResult, 3)
ActiveSheet.Cells(intRow, 5).Value = strTableResult(intIndexResult, 4)
ActiveSheet.Cells(intRow, 6).Value = strTableResult(intIndexResult, 5)
ActiveSheet.Cells(intRow, 7).Value = strTableResult(intIndexResult, 6)
ActiveSheet.Cells(intRow, 8).Value = strTableResult(intIndexResult, 7)
ActiveSheet.Cells(intRow, 9).Value = strTableResult(intIndexResult, 8)
ActiveSheet.Cells(intRow, 10).Value = strTableResult(intIndexResult, 9)
ActiveSheet.Cells(intRow, 11).Value = strTableResult(intIndexResult, 10)
ActiveSheet.Cells(intRow, 12).Value = strTableResult(intIndexResult, 11)
ActiveSheet.Cells(intRow, 13).Value = strTableResult(intIndexResult, 12)
ActiveSheet.Cells(intRow, 14).Value = strTableResult(intIndexResult, 13)
ActiveSheet.Cells(intRow, 15).Value = strTableResult(intIndexResult, 14)
ActiveSheet.Cells(intRow, 16).Value = strTableResult(intIndexResult, 15)
ActiveSheet.Cells(intRow, 17).Value = strTableResult(intIndexResult, 16)
ActiveSheet.Cells(intRow, 18).Value = strTableResult(intIndexResult, 17)
ActiveSheet.Cells(intRow, 19).Value = strTableResult(intIndexResult, 18)
ActiveSheet.Cells(intRow, 20).Value = strTableResult(intIndexResult, 19)
ActiveSheet.Cells(intRow, 21).Value = strTableResult(intIndexResult, 20)
ActiveSheet.Cells(intRow, 22).Value = strTableResult(intIndexResult, 21)
ActiveSheet.Cells(intRow, 23).Value = strTableResult(intIndexResult, 22)
ActiveSheet.Cells(intRow, 24).Value = strTableResult(intIndexResult, 23)
intIndexResult = intIndexResult + 1
intRow = intRow + 1
Loop
End Sub