Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's wrong with this code???
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 -------------------- -- Twain ------------------------------------------------------------------------ Twain's Profile: http://www.excelforum.com/member.php...o&userid=25731 View this thread: http://www.excelforum.com/showthread...hreadid=391698 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's wrong with this code???
********* 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's wrong with this code???
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's wrong with this code???
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 -------------------- -- Twain ------------------------------------------------------------------------ Twain's Profile: http://www.excelforum.com/member.php...o&userid=25731 View this thread: http://www.excelforum.com/showthread...hreadid=391698 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is wrong with this code? | Excel Discussion (Misc queries) | |||
What is wrong with this code? | Excel Discussion (Misc queries) | |||
What's wrong with this code, please? | Excel Programming | |||
What's wrong w/my code? | Excel Programming | |||
Is something wrong with the code | Excel Programming |