I have a sample of some VBA that lookup a concatenation of 2 columns. This looksup a database feed, with a row count from between 35k and 250k.
Doing vlookups is way too slow with times from 60 to 500 seconds. What would be the most efficient way to get the the same result.
Sequence
- Turns of screen updating
- Turns off all calculations
- Disables database
- Clears clipboard cache
- Refreshes db data
- Sets the lookups
- Turns on calculations
- Turns off calculations
- Copy and pastes values of the vlookups.
- Enables database
- Turns everything back on
S
Sub startcom()
Dim ii As Long, lastrow As Long
Dim StartTime As Double
Dim SecondsElapsed As Double
' starts timer
StartTime = Timer
'freeze screens, clears cache and stops cals
stopall
'Set error traps and start and end times
On Error GoTo errortrap:
Set sht1 = wsRag
Set sht2 = wsComdata
sht2.Select
reflist
'Find the last row (in column A) with data. and set start row for data copy
lastrow = sht1.Range("A:A").Find("*", SearchDirection:=xlPrevious).Row
ii = 9
'disables db connection
wsConfig.Cells(7, 2) = 0
sht1.Select
Range("AM" & ii & ":AM" & lastrow).Formula = "=IF(VLOOKUP(CONCATENATE(A"& ii &",B" & ii &"),Comment_data!A:F,4,0)="""","""",VLOOKUP(CONCATENATE(A" & ii & ",B" & ii & "),Comment_data!A:F,4,0))" ' Get comments
calcon
calcoff
Range("AM" & ii & ":AM" & lastrow & "").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'enable DB connection
wsConfig.Cells(7, 2) = 1
'Determine how many seconds code took to run
SecondsElapsed = Round(Timer - StartTime, 2)
'get lenghth of runtime
Debug.Print "Ran successfully in " & SecondsElapsed & " seconds", vbInformation
startall
Exit Sub
errortrap:
errormess
Debug.Print "Location: Comments start"
End Sub