What's wrong with this code???

[Twain]

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

Please Login or Register  to view this content.

[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




"Twain" wrote:

>
> To give you some background on this I am trying to compare values from
> two different workbooks, then if the values match copy over that row
> from Workbook Z to workbook A. I can't seem to spot what I am doing
> 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
>
> --------------------
>
>
> --
> Twain
> ------------------------------------------------------------------------
> Twain's Profile: http://www.excelforum.com/member.php...o&userid=25731
> View this thread: http://www.excelforum.com/showthread...hreadid=391698
>
>

[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" <[email protected]> wrote in
message news:[email protected]...
>
> To give you some background on this I am trying to compare values
> from
> two different workbooks, then if the values match copy over that row
> from Workbook Z to workbook A. I can't seem to spot what I am doing
> 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
>
> --------------------
>
>
> --
> Twain
> ------------------------------------------------------------------------
> Twain's Profile:
> http://www.excelforum.com/member.php...o&userid=25731
> View this thread:
> http://www.excelforum.com/showthread...hreadid=391698
>

[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???

Please Login or Register  to view this content.