| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sloooow loop
I am using the following code and was supprised at how slow it ran.
Can anyone tell me a better (faster way)??? Dim WorkRange As Range If Sheet4.Range("a51").Value = "" Then Set WorkRange = Sheet4.Range("a55").End(xlUp).Offset(1, 0) WorkRange.Offset(0, 0).Value = ufTrustEntry.tbDate.Value WorkRange.Offset(0, 1).Value = ufTrustEntry.cbCustomer.Value WorkRange.Offset(0, 2).Value = ufTrustEntry.cbAccount.Value WorkRange.Offset(0, 3).Value = ufTrustEntry.cbTransaction.Value WorkRange.Offset(0, 4).Value = ufTrustEntry.tbAmount.Value WorkRange.Offset(0, 5).Value = ufTrustEntry.tbAddInfo.Value Else MsgBox "You have exceeded the limit of Current transactions, you must do a 'New Day' to continue.", vbOKOnly + vbExclamation, "Data Limit exceeded" ClearTrans Exit Sub End If Set WorkRange = Nothing |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sloooow loop
The code shouldn't be slow. If you have formulas referring to the cells
being updated, that might be the culprit. Set calculation to manual application.Calculation = xlManual If Sheet4.Range("a51").Value = "" Then Set WorkRange = Sheet4.Range("a55").End(xlUp).Offset(1, 0) WorkRange.Offset(0, 0).Value = ufTrustEntry.tbDate.Value WorkRange.Offset(0, 1).Value = ufTrustEntry.cbCustomer.Value WorkRange.Offset(0, 2).Value = ufTrustEntry.cbAccount.Value WorkRange.Offset(0, 3).Value = ufTrustEntry.cbTransaction.Value WorkRange.Offset(0, 4).Value = ufTrustEntry.tbAmount.Value WorkRange.Offset(0, 5).Value = ufTrustEntry.tbAddInfo.Value Else MsgBox "You have exceeded the limit of Current transactions, you must do a 'New Day' to continue.", vbOKOnly + vbExclamation, "Data Limit exceeded" ClearTrans Exit Sub End If Application.Calculation = xlAutomatic -- Regards, Tom Ogilvy "Christy" wrote in message ... I am using the following code and was supprised at how slow it ran. Can anyone tell me a better (faster way)??? Dim WorkRange As Range If Sheet4.Range("a51").Value = "" Then Set WorkRange = Sheet4.Range("a55").End(xlUp).Offset(1, 0) WorkRange.Offset(0, 0).Value = ufTrustEntry.tbDate.Value WorkRange.Offset(0, 1).Value = ufTrustEntry.cbCustomer.Value WorkRange.Offset(0, 2).Value = ufTrustEntry.cbAccount.Value WorkRange.Offset(0, 3).Value = ufTrustEntry.cbTransaction.Value WorkRange.Offset(0, 4).Value = ufTrustEntry.tbAmount.Value WorkRange.Offset(0, 5).Value = ufTrustEntry.tbAddInfo.Value Else MsgBox "You have exceeded the limit of Current transactions, you must do a 'New Day' to continue.", vbOKOnly + vbExclamation, "Data Limit exceeded" ClearTrans Exit Sub End If Set WorkRange = Nothing |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sloooow loop
Thanks Tom! I actually posted the wrong code but your answer was still
correct. The part that was giving me trouble was similar but included a For-Next loop inside the If-Then. Christy ;) "Tom Ogilvy" wrote: The code shouldn't be slow. If you have formulas referring to the cells being updated, that might be the culprit. Set calculation to manual application.Calculation = xlManual If Sheet4.Range("a51").Value = "" Then Set WorkRange = Sheet4.Range("a55").End(xlUp).Offset(1, 0) WorkRange.Offset(0, 0).Value = ufTrustEntry.tbDate.Value WorkRange.Offset(0, 1).Value = ufTrustEntry.cbCustomer.Value WorkRange.Offset(0, 2).Value = ufTrustEntry.cbAccount.Value WorkRange.Offset(0, 3).Value = ufTrustEntry.cbTransaction.Value WorkRange.Offset(0, 4).Value = ufTrustEntry.tbAmount.Value WorkRange.Offset(0, 5).Value = ufTrustEntry.tbAddInfo.Value Else MsgBox "You have exceeded the limit of Current transactions, you must do a 'New Day' to continue.", vbOKOnly + vbExclamation, "Data Limit exceeded" ClearTrans Exit Sub End If Application.Calculation = xlAutomatic -- Regards, Tom Ogilvy "Christy" wrote in message ... I am using the following code and was supprised at how slow it ran. Can anyone tell me a better (faster way)??? Dim WorkRange As Range If Sheet4.Range("a51").Value = "" Then Set WorkRange = Sheet4.Range("a55").End(xlUp).Offset(1, 0) WorkRange.Offset(0, 0).Value = ufTrustEntry.tbDate.Value WorkRange.Offset(0, 1).Value = ufTrustEntry.cbCustomer.Value WorkRange.Offset(0, 2).Value = ufTrustEntry.cbAccount.Value WorkRange.Offset(0, 3).Value = ufTrustEntry.cbTransaction.Value WorkRange.Offset(0, 4).Value = ufTrustEntry.tbAmount.Value WorkRange.Offset(0, 5).Value = ufTrustEntry.tbAddInfo.Value Else MsgBox "You have exceeded the limit of Current transactions, you must do a 'New Day' to continue.", vbOKOnly + vbExclamation, "Data Limit exceeded" ClearTrans Exit Sub End If Set WorkRange = Nothing |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find loop doesn't loop | Excel Discussion (Misc queries) | |||
| For Each Loop | Excel Discussion (Misc queries) | |||
| Updating range properties is so sloooow!!! | Excel Programming | |||
| Worksheet_Change - loop within a loop | Excel Programming | |||
| HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming | |||
Linear Mode
