Shippwreck
Programmer
Hi,
It would seem to be a simple problem, and i have a working solution, however as with all things it don't work properly (or at least how i want it to)
The problem is that i have 2 spreadsheets that i need to compare. Both have the same column headings and both contain a table that is 350ish rows by 13 columns. What i need to do is compare the two as they both contain staff data and identify joiners and leavers (tables from different quarters) What i decided was to look at each row in the first table one by one and loop through the second table searching. If it found a match it puts the field into "no change" tab, if they are not there it puts the field into "Joiners" tab, and then anyone left must be a leaver.
The second part to this is that i relised i could use this to clean the data records at the same time, so only if all 13 fields are exactly the same will it put it in "no change", that way any left are either leavers or need correct data. (as it happens i only care about the ones that stay the same or are joiners so the fact that all leavers and the ones with incorrect data will be in the same sheet doesn't matter as this sheet will only be deleted anyway)
Due to the size of the sheets and the number of potential comparisons it takes a good 10 min to run so i tried to add a status bar to indicate percent complete. This is where the problem comes, because although the bar works perfectly it only half displays, it seems to show the border for the form, but nothing shows in the form, if you then open up another app and move it over and away from the bar, the rest appears, but that is obviously not workable
I run the macro by calling the form and then the compare loop is called from the form.
The loop and form code is below, (form code in bold), there is obviously a form to this as well but i can't show that. The 2 questions i have are:
Is there anyway to improve the loop first? (I have never written anything like this before and this is my first stab so i'm fully open to the possibilty that it is not very efficient, and this could possibly be the reason for the form not displaying properly)
If that isn't the reason and the code is relatively good then why wont the form display properly and how can i get it to display properly?
Thanks for any help
Shippwreck
It would seem to be a simple problem, and i have a working solution, however as with all things it don't work properly (or at least how i want it to)
The problem is that i have 2 spreadsheets that i need to compare. Both have the same column headings and both contain a table that is 350ish rows by 13 columns. What i need to do is compare the two as they both contain staff data and identify joiners and leavers (tables from different quarters) What i decided was to look at each row in the first table one by one and loop through the second table searching. If it found a match it puts the field into "no change" tab, if they are not there it puts the field into "Joiners" tab, and then anyone left must be a leaver.
The second part to this is that i relised i could use this to clean the data records at the same time, so only if all 13 fields are exactly the same will it put it in "no change", that way any left are either leavers or need correct data. (as it happens i only care about the ones that stay the same or are joiners so the fact that all leavers and the ones with incorrect data will be in the same sheet doesn't matter as this sheet will only be deleted anyway)
Due to the size of the sheets and the number of potential comparisons it takes a good 10 min to run so i tried to add a status bar to indicate percent complete. This is where the problem comes, because although the bar works perfectly it only half displays, it seems to show the border for the form, but nothing shows in the form, if you then open up another app and move it over and away from the bar, the rest appears, but that is obviously not workable
I run the macro by calling the form and then the compare loop is called from the form.
The loop and form code is below, (form code in bold), there is obviously a form to this as well but i can't show that. The 2 questions i have are:
Is there anyway to improve the loop first? (I have never written anything like this before and this is my first stab so i'm fully open to the possibilty that it is not very efficient, and this could possibly be the reason for the form not displaying properly)
If that isn't the reason and the code is relatively good then why wont the form display properly and how can i get it to display properly?
Code:
For ccrownum = 2 To cclastrow
For dhrownum = 2 To dhlastrow
If Match = 13 Then
Exit For
End If
Match = 0
For cccolumnnum = 1 To cclastcolumnnum
Sheets("Current Staff").Select
Range(Chr$(cccolumnnum + 64) & ccrownum).Select
ccRecord = ActiveCell.Value
Sheets("DH Return").Select
Range(Chr$(cccolumnnum + 64) & dhrownum).Select
If ActiveCell.Value = ccRecord Then
Match = Match + 1
If Match = cclastcolumnnum Then
Rows(dhrownum & ":" & dhrownum).Select
Selection.Cut
Sheets("No Change").Select
Range("A1").Select
Selection.End(xlDown).Select
If ActiveCell.Row = 65536 Then
Range("A2").Select
Else
ActiveCell.Offset(1, 0).Select
End If
ActiveSheet.Paste
Match = 0
Exit For
End If
Else
Exit For
End If
Next cccolumnnum
Next dhrownum
Sheets("Current Staff").Select
Rows(ccrownum & ":" & ccrownum).Select
Selection.Cut
Sheets("Joiners").Select
Range("A1").Select
Selection.End(xlDown).Select
If ActiveCell.Row = 65536 Then
Range("A2").Select
Else
ActiveCell.Offset(1, 0).Select
End If
ActiveSheet.Paste
[b]PctDone = ccrownum / cclastrow
With UserForm1
.Label1.Caption = Format("Percent complete")
.FrameProgress.Caption = Format(PctDone, "0%")
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
End With[/b]
Next ccrownum
[b]Unload UserForm1[/b]Thanks for any help
Shippwreck
