Requesting advice on VLOOKUP alternative.

[Random]

Does anyone have any advice on how to solve a problem with VLOOKUPS
and slow computers?

Problem: Large spreadsheet (35,000 rows, 15 columns Static) and
medium spreadsheet (4500 rows, 4 columns and growing). The smaller
spreadsheet needs to pull matching information from the larger sheet.
However, having VLOOKUP in all of the rows caused the comp to crawl
(5-10 minutes to add a cell). I tried turning off Auto-Calculate, it
helped, but when I updated..... stalled again.

My initial solution was to create a Worksheet_Change sub that
temporarily inserted the VLOOKUP formula into the cells on the row
that data had been entered into and then copy/paste special->values.

My thought was that the lack of numerous VLOOKUPS would speed up the
entire sheet, which it did.

ok.. so now to the questions: This seems like a rather cumbersome way
to accomplish this task.

1. Does anyone have any suggestions on how to accomplish the same
thing without resorting to inserting formulas and then pasting over
them?

2. Is there a way to turn off the screen update during a sub? So
that the user cannot see the cells being updated, but only the final
results?

Thanks to everyone for their time and thoughts.

Random

 

[KevinB]

The screen updating issue I can help you with, the rest I
don't know.

Application.ScreenUpdating = False
... other code ...
Application.ScreenUpdating = True

KB

 

[Random]

Thanks for the response. That sped the process up a bit.

Random

 

[Jose Rojas]

Why not to use a Access Table to store all of your data and use either DAO
or ADO to retieve by SQL statements only the recordsets to work with, that
way you keep integrity on your data.

 

[Random]

Ah... A man after my own heart.... That was my first suggestion to the
jacka$$es in management, but of course they know more than me about
IT. I tried and tried to get them to let me convert their precious
spreadsheets to a SQL database, but they don't quite understand Sequel
and dug their heels in.

I could use the excel ODBC driver to do the same thing though..... not
a bad idea. Do you think that would increase performance over a
VLOOKUP call to a closed remote spreadsheet?


Random

 

[RB Smissaert]

Another option would be to run loops in arrays. Look at the following
posting:

http://groups.google.com/groups?hl=...=*excel*&as_uauthors=RB%20Smissaert&lr=&hl=en

If this link doesn't work do a search for Lookup, RB Smissaert in this
group.


RBS

 

[Trevor Shuttleworth]

Random

rather than insert the formula and then copy and paste the value, just
insert the value:

Private Sub Worksheet_Change(ByVal Target As Range)
' Check column being changed
If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub ' adjust
column to suit
' Insert formula in next column
' Target.Offset(0, 1).Formula = "=VLOOKUP(" & Target.Address &
",$F$9:$G$11,2,FALSE)"

' Alternatively, insert the value of the VLOOKUP into the next column
On Error Resume Next
Target.Offset(0, 1).Value = _
Application.WorksheetFunction.VLookup _
(Target, Range("$F$9:$G$11"), 2, False)
On Error GoTo 0
End Sub

Regards

Trevor

 

[whisperer]

To switch off the screen updating place the following line at the start
of your sub with its counterpart as the last line of your sub.

Application.ScreenUpdating = False
...
Application.ScreenUpdating = True

HTH

 

[Andrew]

When I first thought about this I thought the main drawback was having
to have the workbook open to perform the lookup but after a bit more
thought and investigation I found that this is not the case. Check
out the following. Credit to John Walkenbach for the xl4macro
approach.

Regards,
Andrew

Function LookupFromClosedWb(LookupFilePath As String, LookupFileName
As String, LookupSht As String, _
LookupArrayRef As String, LookupValue As Variant, ColIndex As
Long) As Variant

Dim strArrayRef As String
Dim strMacroArg As String

' Build the full reference to the lookup array from path, file and
cell ref
strArrayRef = "'" & LookupFilePath & "[" & LookupFileName & "]" &
LookupSht & "'!" & _
Range(LookupArrayRef).Address(, , xlR1C1)
Debug.Print strArrayRef
' Build the xl4 macro strg based on lookup function that we want to
run
strMacroArg = "VLookup(" & LookupValue & "," & strArrayRef & "," &
ColIndex & _
"," & False & ")"
Debug.Print strMacroArg
' Run the macro, return the value
LookupFromClosedWb = ExecuteExcel4Macro(strMacroArg)

End Function

Sub test()
' Lookup a value based on the value in A1 of my active wb
Range("A10").Value = LookupFromClosedWb("c:\temp\", _
"vlookup.xls", "sheet1", "A1:B10", Range("A1"), 2)
End Sub

 

[Random]

This is exactly what I was looking for. Thanks for the advice!

Random

 

[Trevor Shuttleworth]

You're welcome.

Trevor

This is exactly what I was looking for. Thanks for the advice!

Random