Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
multi sheet scanning
Hi,
My last step about my software audit report is to allocate some basic information to each software detected and relative to relative people/computer. for example : Sheet1 : i have 21.000 records(rows) as following : --------------------------------------------------- column A : SLA status e.g. : "standard", "standard optional", or empty) column B : License status e.g. : empty if column A = standard or standard optional, else "licensed", "illegal", '"deleted", "needed" column C : publisher column D : Software name column E : version column F : computer_barcode column G : user name Sheet2 : list of SLA status --------------------------- column A : publisher column B : Software name column C : status Sheet3 : list of previous audit with existing info about SLA status and License status ----------------------------------------------------------------------- on this sheet, it's the same as Sheet one except that we already perform merging of sheet1 and data from sheet2. this sheet contains previous audit report and should be used to not redo merging of sheet1 and sheet2 for all records of sheet1. the process : ------------- the process is the following : for each record of sheet1, code should check if (publisher+software_name+version+computer_barcode) of sheet1 already exists in sheet3. - if yes, so (SLA status and License status) from sheet3 is copied to sheet1 (column A and B). - if no, code should check in sheet2 if (publisher+software_name) of sheet1 exist. - if yes, SLA status from sheet2 is copied to (sheet1.column A) - if no, nothing happen. for 21.000 records my actual code needs around 1:15 hours to scan everything and add missing info. this is really too long. Thus, i'm looking for a better process or maybe a better way how to do it. I use till now some basic comparison like if (thisworkbook.sheet1.range("A2").value = thisworkbook.sheet3.range("A56").value) then .... but this is too long. Has someone an idea how to improve code speed ? thanks a lot, Al. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
multi sheet scanning
Alain:
You could try referencing the worksheets as follows: dim ws1 as worksheet,ws2 as worksheet,ws3 as worksheet set ws1= thisworkbook.sheet1 set ws2= thisworkbook.sheet2 set ws3= thisworkbook.sheet3 then you can reference the cells with if ws1.range("A2").value = ws3.range("A56").value then I assume that you have application.screenupdating=false and dim calcstate calcstate = Application.Calculation Application.Calculation = xlCalculationManual at the start of the procedure and Application.Calculation = calcstate application.screenupdating = true at the end. Trying this may help. The other way is to do larger copies but it really depends on your code. -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "--== Alain ==--" wrote: Hi, My last step about my software audit report is to allocate some basic information to each software detected and relative to relative people/computer. for example : Sheet1 : i have 21.000 records(rows) as following : --------------------------------------------------- column A : SLA status e.g. : "standard", "standard optional", or empty) column B : License status e.g. : empty if column A = standard or standard optional, else "licensed", "illegal", '"deleted", "needed" column C : publisher column D : Software name column E : version column F : computer_barcode column G : user name Sheet2 : list of SLA status --------------------------- column A : publisher column B : Software name column C : status Sheet3 : list of previous audit with existing info about SLA status and License status ----------------------------------------------------------------------- on this sheet, it's the same as Sheet one except that we already perform merging of sheet1 and data from sheet2. this sheet contains previous audit report and should be used to not redo merging of sheet1 and sheet2 for all records of sheet1. the process : ------------- the process is the following : for each record of sheet1, code should check if (publisher+software_name+version+computer_barcode) of sheet1 already exists in sheet3. - if yes, so (SLA status and License status) from sheet3 is copied to sheet1 (column A and B). - if no, code should check in sheet2 if (publisher+software_name) of sheet1 exist. - if yes, SLA status from sheet2 is copied to (sheet1.column A) - if no, nothing happen. for 21.000 records my actual code needs around 1:15 hours to scan everything and add missing info. this is really too long. Thus, i'm looking for a better process or maybe a better way how to do it. I use till now some basic comparison like if (thisworkbook.sheet1.range("A2").value = thisworkbook.sheet3.range("A56").value) then .... but this is too long. Has someone an idea how to improve code speed ? thanks a lot, Al. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why does a Multi-sheet/user file open on sheet two every time? | Excel Discussion (Misc queries) | |||
countif in multi sheet | Excel Discussion (Misc queries) | |||
Help with multi sheet workbook | Excel Worksheet Functions | |||
brinnging data to excel via scanning a sheet. | New Users to Excel | |||
Multi Sheet Macro | Excel Programming |