BravesPiano5
10-17-2013, 02:34 PM
Hello,
I'm looking for a faster way to execute the below code; it works exactly like I want it to (as far as execution) but the length of time it takes to finish is quite lengthy as the data usually at least 100K+ rows.
I've cleaned up and tried to reduce as much unnecessary code as possible...I haven't used arrays before so I researched a bit and tried it out but after a couple of tests, I don't see any performance gain with my coding. Any tips (thanks in advance!)? :*)
**Note=>
Enable events, screen updating & display alerts are turned off at the start.
LastRow is the variable declared to represent the last row# data appears in within the sheet
FundLookup & ProdLookup range variables are declared before this loop
For NumRows = 8 To LastRow
'Start loop at row# 8 as that's where table begins
Application.StatusBar = "Formulating resolution date and product info; currently @ row#" & NumRows & "..."
'Give update to user
Select Case Cells(NumRows, 13)
Case "ZCBF": Cells(NumRows, 14) = "Fund"
Case "ZCBP": Cells(NumRows, 14) = "Promotion"
Case Else: Cells(NumRows, 14) = "#"
End Select
'Identify type of transaction
If Cells(NumRows, 25) = "#" Then
Cells(NumRows, 30) = Cells(NumRows, 26)
Else
Cells(NumRows, 30) = Cells(NumRows, 25)
End If
'Formula for Resolution Date
On Error Resume Next
If Cells(NumRows, 19) = "#" Then
Cells(NumRows, 27) = WorksheetFunction.VLookup(Cells(NumRows, 22), ProdLookup, 2, 0)
Cells(NumRows, 28) = "#"
Cells(NumRows, 29) = "#"
If Cells(NumRows, 27) = "" Then Cells(NumRows, 27) = "#"
Else
Cells(NumRows, 27) = WorksheetFunction.VLookup(Cells(NumRows, 19), FundLookup, 3, 0)
Cells(NumRows, 28) = WorksheetFunction.VLookup(Cells(NumRows, 19), FundLookup, 7, 0)
Cells(NumRows, 29) = WorksheetFunction.VLookup(Cells(NumRows, 19), FundLookup, 4, 0)
If Cells(NumRows, 27) = "" Then Cells(NumRows, 27) = "#"
If Cells(NumRows, 28) = "" Then Cells(NumRows, 28) = "#"
If Cells(NumRows, 29) = "" Then Cells(NumRows, 29) = "#"
End If
'If item has a fund ID, grab the appropriate segment & category...if not, look up segment by brand
On Error GoTo 0
Next
I'm looking for a faster way to execute the below code; it works exactly like I want it to (as far as execution) but the length of time it takes to finish is quite lengthy as the data usually at least 100K+ rows.
I've cleaned up and tried to reduce as much unnecessary code as possible...I haven't used arrays before so I researched a bit and tried it out but after a couple of tests, I don't see any performance gain with my coding. Any tips (thanks in advance!)? :*)
**Note=>
Enable events, screen updating & display alerts are turned off at the start.
LastRow is the variable declared to represent the last row# data appears in within the sheet
FundLookup & ProdLookup range variables are declared before this loop
For NumRows = 8 To LastRow
'Start loop at row# 8 as that's where table begins
Application.StatusBar = "Formulating resolution date and product info; currently @ row#" & NumRows & "..."
'Give update to user
Select Case Cells(NumRows, 13)
Case "ZCBF": Cells(NumRows, 14) = "Fund"
Case "ZCBP": Cells(NumRows, 14) = "Promotion"
Case Else: Cells(NumRows, 14) = "#"
End Select
'Identify type of transaction
If Cells(NumRows, 25) = "#" Then
Cells(NumRows, 30) = Cells(NumRows, 26)
Else
Cells(NumRows, 30) = Cells(NumRows, 25)
End If
'Formula for Resolution Date
On Error Resume Next
If Cells(NumRows, 19) = "#" Then
Cells(NumRows, 27) = WorksheetFunction.VLookup(Cells(NumRows, 22), ProdLookup, 2, 0)
Cells(NumRows, 28) = "#"
Cells(NumRows, 29) = "#"
If Cells(NumRows, 27) = "" Then Cells(NumRows, 27) = "#"
Else
Cells(NumRows, 27) = WorksheetFunction.VLookup(Cells(NumRows, 19), FundLookup, 3, 0)
Cells(NumRows, 28) = WorksheetFunction.VLookup(Cells(NumRows, 19), FundLookup, 7, 0)
Cells(NumRows, 29) = WorksheetFunction.VLookup(Cells(NumRows, 19), FundLookup, 4, 0)
If Cells(NumRows, 27) = "" Then Cells(NumRows, 27) = "#"
If Cells(NumRows, 28) = "" Then Cells(NumRows, 28) = "#"
If Cells(NumRows, 29) = "" Then Cells(NumRows, 29) = "#"
End If
'If item has a fund ID, grab the appropriate segment & category...if not, look up segment by brand
On Error GoTo 0
Next