| Home |
| Search |
| Today's Posts |
|
|
|
#1
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 | |||
Hybrid Mode
