|
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 |
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 |
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 |
| All times are GMT +1. The time now is 01:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com