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