Check no. of records have copied correctly

[Stuart]

If anyone could help with this. it would be much appreciated:

User's workbook has sheets with data in cols A:G
They 'mark' the rows to be copied with a capital letter in
either col H, I, or J. So if row 50 had 'A' in col H and
'K' in col J, then user wishes to send that record to
Clients 'A' and 'K'.
A Collection (mstrList) is built, containing the unique values
found in cols H:J and the values are then loaded into a
multicolumn listbox in a form:
' Add the sorted, non-duplicated items to a ListBox
For Each Item In mstrList
frmName_Contractors.lbDataCode.AddItem Item
Next Item
The user can now enter the true Client name against the
relevenr record identifier ( A, B, Z etc).
A new workbook is created, containing only worksheets
in 'mstrList' and named accordingly (A, B, Z etc).
Code then runs on the original workbook, sampling through
cols H:J and copying records to the new workbook,
such that all records tagged 'A' end up in newbook
sheet name 'A' etc, etc.
This code also copies any values in cols "H:J".

What I would like to do is to crosscheck the number of
records tagged 'A' (or whatever) in the original book, with
the records found in newbook(sheetA) or whatever.

It seems to me that I could use the values in 'mstrList' to
count each instance of each value in cols "H:J" in the
original book, and then do the same in the new book.

If so, then how could I achieve this, and also compare
and alert if there is a problem, please?

Regards.

 

[Tom Ogilvy]

set srcSh = Worksheets("Whatever") ' sheet with original H:I data
for i = 0 to frmName_Contractors.lbDataCode.Listcount-1
sName = frmName_Contractors.lbDataCode.List(i,0)
num = Application.Countif(srcSh.Range("H1:J50"),sName)
set sh = Workbooks("otherbook.xls").Worksheets(sName)
if sh.cells(rows.count,1).end(xlup).Row <> num then
msgbox "Problems"
' if you have header rows in the sheet, adjust the comparison
' to account for these
end if
Next

or

for each itm in mstrList
sName = itm
num = Application.Countif(srcSh.Range("H1:J50"),sName)
set sh = Workbooks("otherbook.xls").Worksheets(sName)
if sh.cells(rows.count,1).end(xlup).Row <> num then
msgbox "Problems"
' if you have header rows in the sheet, adjust the comparison
' to account for these
end if
Next

 

[Stuart]

Many thanks.

Regards.

set srcSh = Worksheets("Whatever") ' sheet with original H:I data
for i = 0 to frmName_Contractors.lbDataCode.Listcount-1
sName = frmName_Contractors.lbDataCode.List(i,0)
num = Application.Countif(srcSh.Range("H1:J50"),sName)
set sh = Workbooks("otherbook.xls").Worksheets(sName)
if sh.cells(rows.count,1).end(xlup).Row <> num then
msgbox "Problems"
' if you have header rows in the sheet, adjust the comparison
' to account for these
end if
Next

or

for each itm in mstrList
sName = itm
num = Application.Countif(srcSh.Range("H1:J50"),sName)
set sh = Workbooks("otherbook.xls").Worksheets(sName)
if sh.cells(rows.count,1).end(xlup).Row <> num then
msgbox "Problems"
' if you have header rows in the sheet, adjust the comparison
' to account for these
end if
Next