multi sheet scanning

[--== Alain ==--]

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.

 

[Martin Fishlock]

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.