Sheets.Cells.Value = Sheets.Cells.Value Should this be very slow?
 

Dear All,


Below is my code for comparing 2 lists.
As i am working in an enviroment with very small dataresources, i am
used to things being slow... However, my code is ultra slow atm. Any
ideas on speeding it up or should i call my techsupport *again* to ask
for more memory?

Dim lastRow As Long, rw As Long
Dim rngA As Range ' rngA = first list of data

Set rngA = Range(Sheets("Deltas").Cells(1, "A"),
Sheets("Deltas").Cells(Rows.Count, "A").End(xlUp))

lastRow = Sheets("ODIS").Cells(Rows.Count, "A").End(xlUp).Row ' = end
of 2nd list of records
MsgBox lastRow
'rw writes the results on Deltas
rw = Sheets("Deltas").Cells(1, "E").End(xlUp).Row
'MsgBox rw

For i = 1 To lastRow Step 1
If Application.CountIf(rngA, Sheets("Deltas").Cells(i, "F").Value)
= 0 Then
Sheets("Deltas").Cells(rw, "G").Value =
Sheets("ODIS").Cells(i, "C").Value
Sheets("Deltas").Cells(rw, "H").Value =
Sheets("ODIS").Cells(i, "E").Value
Sheets("Deltas").Cells(rw, "I").Value =
Sheets("ODIS").Cells(i, "G").Value
Sheets("Deltas").Cells(rw, "J").Value =
Sheets("ODIS").Cells(i, "H").Value
rw = rw + 1
End If
Next

Sheets.Cells.Value = Sheets.Cells.Value Should this be very slow?
 

On 4 Apr, 08:19, "artisdepartis" wrote:
Dear All,

Below is my code for comparing 2 lists.
As i am working in an enviroment with very small dataresources, i am
used to things being slow... However, my code is ultra slow atm. Any
ideas on speeding it up or should i call my techsupport *again* to ask
for more memory?

Dim lastRow As Long, rw As Long
Dim rngA As Range ' rngA = first list of data

Set rngA = Range(Sheets("Deltas").Cells(1, "A"),
Sheets("Deltas").Cells(Rows.Count, "A").End(xlUp))

lastRow = Sheets("ODIS").Cells(Rows.Count, "A").End(xlUp).Row ' = end
of 2nd list of records
MsgBox lastRow
'rw writes the results on Deltas
rw = Sheets("Deltas").Cells(1, "E").End(xlUp).Row
'MsgBox rw

For i = 1 To lastRow Step 1
If Application.CountIf(rngA, Sheets("Deltas").Cells(i, "F").Value)
= 0 Then
Sheets("Deltas").Cells(rw, "G").Value =
Sheets("ODIS").Cells(i, "C").Value
Sheets("Deltas").Cells(rw, "H").Value =
Sheets("ODIS").Cells(i, "E").Value
Sheets("Deltas").Cells(rw, "I").Value =
Sheets("ODIS").Cells(i, "G").Value
Sheets("Deltas").Cells(rw, "J").Value =
Sheets("ODIS").Cells(i, "H").Value
rw = rw + 1
End If
Next

If you find your code to be a bit on the slow side, then you could
always try processing the lists as arrays. Read the lists off the
worksheets, do all the comparisons and copying in an array, then write
the results back to the worksheet. Should speed things up.

Sheets.Cells.Value = Sheets.Cells.Value Should this be very slow?
 

Try first turning off screenupdating and switching calculation to manual (don't forget to switch back at the end)

--
Tim Williams
Palo Alto, CA


"artisdepartis" wrote in message oups.com...
Dear All,


Below is my code for comparing 2 lists.
As i am working in an enviroment with very small dataresources, i am
used to things being slow... However, my code is ultra slow atm. Any
ideas on speeding it up or should i call my techsupport *again* to ask
for more memory?

Dim lastRow As Long, rw As Long
Dim rngA As Range ' rngA = first list of data

Set rngA = Range(Sheets("Deltas").Cells(1, "A"),
Sheets("Deltas").Cells(Rows.Count, "A").End(xlUp))

lastRow = Sheets("ODIS").Cells(Rows.Count, "A").End(xlUp).Row ' = end
of 2nd list of records
MsgBox lastRow
'rw writes the results on Deltas
rw = Sheets("Deltas").Cells(1, "E").End(xlUp).Row
'MsgBox rw

For i = 1 To lastRow Step 1
If Application.CountIf(rngA, Sheets("Deltas").Cells(i, "F").Value)
= 0 Then
Sheets("Deltas").Cells(rw, "G").Value =
Sheets("ODIS").Cells(i, "C").Value
Sheets("Deltas").Cells(rw, "H").Value =
Sheets("ODIS").Cells(i, "E").Value
Sheets("Deltas").Cells(rw, "I").Value =
Sheets("ODIS").Cells(i, "G").Value
Sheets("Deltas").Cells(rw, "J").Value =
Sheets("ODIS").Cells(i, "H").Value
rw = rw + 1
End If
Next