Vlookup Causing Slow Calculations

jbilkes · Sep 24th 2007

Hi,
I am trying to lookup data series via a key(ISIN or Valor). I have built a macro that works but it is very slow when I use it for a larger file. I have attached a small example.
(normal file is about 15000 line)

Maybe there is a way to return a complete data row after identifying a match.

Your help is very much appreciated.
Kind regards
Joost

This is the code:
(also in attachment with data sample)

Code

Sub retrieveSeries()
' start


jRow = 10
    jCol = 1
    lRow = 8
    lCol = 2
    sWK1 = "output"
    sWK2 = "series"
   


    Sheets(sWK1).Activate
    Do Until Cells(jRow, jCol) = ""
        Do Until lRow = 20000 Or Sheets(sWK2).Cells(lRow, lCol) = Cells(jRow, jCol) ' first search for valor
            lRow = lRow + 1
        Loop
        If lRow = 20000 Then ' valor not found
                lRow = 8
            Do Until lRow = 20000 Or Sheets(sWK2).Cells(lRow, lCol - 1) = Cells(jRow, jCol + 1) 'search for isin
                lRow = lRow + 1
            Loop
            If lRow = 20000 Then
                lCol = 2 'dataseries NOT found
            Else
                Call retrieveLNofSeries ' retrieve series via ISIN
            End If
         Else
            Call retrieveLNofSeries ' retrieve series via VALOR
         End If
    lRow = 8
    jRow = jRow + 1
    Loop
   
End Sub


Sub retrieveLNofSeries()
'****Retrieves the LN of the serie****
 
Do Until Application.WorksheetFunction.IsNumber(Sheets(sWK2).Cells(lRow, lCol + 3)) = False
    Cells(jRow, jCol + 3) = Application.WorksheetFunction.Ln((Sheets(sWK2).Cells(lRow, lCol + 3) / 100) + 1) ' calculates the lnof the data
    jCol = jCol + 1
    lCol = lCol + 1
Loop


Cells(jRow, 11) = Sheets(sWK2).Cells(lRow, 12) 'return size
lCol = 2
jCol = 1
End Sub

Display More

Sicarii · Sep 24th 2007

Re: Speed Up A Lookup Macro

Have you tried VLOOKUP?

jbilkes · Sep 24th 2007

Re: Speed Up A Lookup Macro

Thanks
Initially it was a vlookup but the file became too large and very slow.
The vlookup caused very long re-calculations.

Isn't there a way to lookup a value in column a and then return the complete series from another table via a macro?

Thanks for your Help!!

**norie** · Sep 24th 2007

Re: Speed Up A Lookup Macro

How about filters?

jbilkes · Sep 24th 2007

Re: Speed Up A Lookup Macro

I really think I have to include it in teh existing macros in this file.
I am not sure if I fully understand your sugegstion.

This file is linked to a db via a pivot. So first you have to find a group of "lookup items(isins/valors)" via the pivot. Step 2 is what I showed in the macro; you have to retrieve the performance-series over time(120 periods) for this "group of isins/valors"

cheers

**norie** · Sep 24th 2007

Re: Speed Up A Lookup Macro

If data is being pulled from a database can't you query that database?

jbilkes · Sep 24th 2007

Re: Speed Up A Lookup Macro

The database just has the names (isin/valors) and classifications of the lookup items(investment products). The performance data comes from another source. So basically I filter the database in order to get a peergroup with the same classifications and then I lookup the corresponding performance rows in the performance datafiles("series" in my example)

Cheers

turtle44 · Sep 25th 2007

Re: Speed Up A Lookup Macro

Your code below seems to be looking at 20,000 cells and comparing to a set value.

Code

Do Until lRow = 20000 Or Sheets(sWK2).Cells(lRow, lCol) = Cells(jRow, jCol) ' first  search for valor
lRow = lRow + 1 
Loop

First of all, it seems like 20,000 is an arbitrary number, and you are probably searching thru a lot of blank cells. Instead of using 20,000, you should probably stop at the last used cell.

Secondly, it seems like it would be a lot quicker to use Find, e.g.

Code

Dim rng As Range
With Sheets(sWK2)
Set rng = Range(.Cells(lRow, lCol), .Cells(.Cells.Rows.Count, lCol).End(xlUp))
lRow = rng.Find(What:=Cells(jRow, jCol), After:=.Cells(lRow, lCol), _
      LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
End With

Dave Hawley · Sep 25th 2007

Re: Speed Up A Lookup Macro

Native Excel Functions are ALWAYS much faster than a replacement Custom Function.

The single best way to speed up VLOOKUP is to sort the left most column and use True for Range_lookup, or omit it.

The minute recalculations become longer than about 4-6 secs, you have a Spreadsheet Design issue that should be fixed and NEVER catered to.

jbilkes · Sep 25th 2007

Re: Vlookup Causing Slow Calculations

Thanks
The workbook is probably wrongly designed (i.e. changing pivot that causes 20000 rows and 120 column to refresh). The vlookups take much longer than 6 secs. Unfortunately I can't change the design, there are too many dependencies.

Thanks for the Find tip turtle44. This works perfectly and much faster.

Code

Sub Find_Value()
Dim iRow As Integer, iCol As Integer, jRow  As Integer, jCol As Integer, sDetail As String
Dim C, firstaddress


Application.ScreenUpdating = False






iRow = 10 'sets it at the first detail


Do Until Worksheets("Output").Cells(iRow, 3) = "" 'loops through the values until there are no more


sDetail = Worksheets("Output").Cells(iRow, 1)


    
        
            With Worksheets("Series").Range("A8:B13")
                Set C = .Find(sDetail, LookIn:=xlValues) 'find detail
                
                If Not C Is Nothing Then
                    firstaddress = C.Address
                    iCol = 5
                    Do
                        'loop through the details adding the to the sheet
                        Do Until iCol = 13
                            Worksheets("Output").Cells(iRow, iCol) = C.Offset(0, iCol - 2)
                            iCol = iCol + 1
                        Loop
                        
                    Loop While Not C Is Nothing And C.Address <> firstaddress
                   
                    iRow = iRow + 1
                Else '******** this is the bit added to take into acount the cell being blank
                    sDetail = Worksheets("Output").Cells(iRow, 2)
                    Set C = .Find(sDetail, LookIn:=xlValues) 'find detail
                    If Not C Is Nothing Then
                        firstaddress = C.Address
                        iCol = 5
                    Do
                        'loop through the details adding the sheet
                        Do Until iCol = 13
                            Worksheets("Output").Cells(iRow, iCol) = C.Offset(0, iCol - 1)
                            iCol = iCol + 1
                        Loop
                        
                    Loop While Not C Is Nothing And C.Address <> firstaddress
                   
                    iRow = iRow + 1
                    End If
                    iRow = iRow + 1
                End If
                   
            End With
            
        
    


Loop




End Sub

Display More

Dave Hawley · Sep 25th 2007

Re: Vlookup Causing Slow Calculations

I would suggest you read up on the FIND METHOD if you are going to use that code. Some options MUST be set each time the Find Method is used.

You will also see that COUNTIF can be used to restrict the loop.

filo65 · Sep 25th 2007

Re: Vlookup Causing Slow Calculations

Dave,

Quote

Native Excel Functions are ALWAYS much faster than a replacement Custom Function

I don't agree with you; certainly they are more practical because they save you plenty of typing, but please take a look at this example:

Code

Sub excelfunction_test()
  Dim i&, imax&
  Dim tim#, tmp#
  imax = 5000000
  
  tim = Now
  tmp = 0
  
  For i = 1 To imax Step 1
    tmp = d_min(tmp, i)
  Next i
  
  Cells(1, 1) = "Custom"
  Cells(1, 2) = Format(Now - tim, "hh:mm:ss")
  
  
  tim = Now
  tmp = 0
  
  For i = 1 To imax Step 1
    tmp = WorksheetFunction.Min(tmp, i)
  Next i
  
  Cells(3, 1) = "Excel Built-in"
  Cells(3, 2) = Format(Now - tim, "hh:mm:ss")
  
End Sub


Private Function d_min(a, b)
  If a < b Then d_min = a Else d_min = b
End Function

Display More

Custom vs Built-in: 14:1 Speed gain.

filippo

Dave Hawley · Sep 26th 2007

Re: Vlookup Causing Slow Calculations

Do tell me how you are testing and what you are testing against? Your post is very vague.

Vlookup Causing Slow Calculations

Participate now!

Share