Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
format cells in 30 different sheets | Excel Discussion (Misc queries) | |||
cells and sheets, names | Excel Worksheet Functions | |||
same info different sheets different cells | Excel Programming | |||
On MouseOver for Cells/Sheets | Excel Programming | |||
the sum of cells of many sheets | Excel Programming |