Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requesting advice on VLOOKUP alternative.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requesting advice on VLOOKUP alternative.
The screen updating issue I can help you with, the rest I
don't know. Application.ScreenUpdating = False ... other code ... Application.ScreenUpdating = True KB -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requesting advice on VLOOKUP alternative.
Thanks for the response. That sped the process up a bit.
Random On Wed, 27 Aug 2003 17:21:19 -0700, "KevinB" wrote: The screen updating issue I can help you with, the rest I don't know. Application.ScreenUpdating = False ... other code ... Application.ScreenUpdating = True KB -----Original Message----- 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requesting advice on VLOOKUP alternative.
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" <Random@nwhere wrote in message ... Thanks for the response. That sped the process up a bit. Random On Wed, 27 Aug 2003 17:21:19 -0700, "KevinB" wrote: The screen updating issue I can help you with, the rest I don't know. Application.ScreenUpdating = False ... other code ... Application.ScreenUpdating = True KB -----Original Message----- 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requesting advice on VLOOKUP alternative.
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 On Wed, 27 Aug 2003 19:47:58 -0500, "Jose Rojas" wrote: 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" <Random@nwhere wrote in message .. . Thanks for the response. That sped the process up a bit. Random On Wed, 27 Aug 2003 17:21:19 -0700, "KevinB" wrote: The screen updating issue I can help you with, the rest I don't know. Application.ScreenUpdating = False ... other code ... Application.ScreenUpdating = True KB -----Original Message----- 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requesting advice on VLOOKUP alternative.
Another option would be to run loops in arrays. Look at the following
posting: http://groups.google.com/groups?hl=e...r%3D%26hl%3Den If this link doesn't work do a search for Lookup, RB Smissaert in this group. RBS "Random" <Random@nwhere wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requesting advice on VLOOKUP alternative.
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 "Random" <Random@nwhere wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requesting advice on VLOOKUP alternative.
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:) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requesting advice on VLOOKUP alternative.
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 "Trevor Shuttleworth" wrote in message ... 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 "Random" <Random@nwhere wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requesting advice on VLOOKUP alternative.
This is exactly what I was looking for. Thanks for the advice!
Random On Thu, 28 Aug 2003 19:06:17 +0100, "Trevor Shuttleworth" wrote: 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 "Random" <Random@nwhere wrote in message .. . 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requesting advice on VLOOKUP alternative.
You're welcome.
Trevor "Random" <Random@nwhere wrote in message ... This is exactly what I was looking for. Thanks for the advice! Random On Thu, 28 Aug 2003 19:06:17 +0100, "Trevor Shuttleworth" wrote: 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 "Random" <Random@nwhere wrote in message .. . 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup alternative | Excel Discussion (Misc queries) | |||
alternative to VLOOKUP | Excel Worksheet Functions | |||
Which is faster? SUMPRODUCT or VLOOKUP, or another alternative? | Excel Worksheet Functions | |||
Vlookup Alternative Needed | Excel Discussion (Misc queries) | |||
Nested Vlookup or alternative? | Excel Worksheet Functions |