Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check no. of records have copied correctly
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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.515 / Virus Database: 313 - Release Date: 01/09/2003 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check no. of records have copied correctly
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 -- Regards, Tom Ogilvy Stuart wrote in message ... 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.515 / Virus Database: 313 - Release Date: 01/09/2003 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check no. of records have copied correctly
Many thanks.
Regards. "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy Stuart wrote in message ... 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.515 / Virus Database: 313 - Release Date: 01/09/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.515 / Virus Database: 313 - Release Date: 01/09/2003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copied info for chart does not show date correctly | Charts and Charting in Excel | |||
Delete records when certain records have duplicate column data | New Users to Excel | |||
Copied formula produces unexpected copied results | New Users to Excel | |||
Check if Conditional Format is True or False / Check cell Color | Excel Worksheet Functions | |||
Check and Remove Records | Excel Discussion (Misc queries) |