How to Find Data in another Worksheet without activating it?

[HumanJHawkins]

I am attempting to create a "config" spreadsheet where parameters for
another spreadsheet can be looked up. I can do this easily if I
activate the other worksheet. And, I can refer directly to the other
worksheet's cells without activating them if I already know the
coordinates of the cell to look at.

But I can't for example, search for a word in column 1 of another
worksheet and then get the value of the 5th column over where that
value was found, unless I activate the other worksheet first.

For example, imagine there is a combo box on the first worksheet with
"Big", "Medium", and "Small". And various calculations are to be done
on the first worksheet based on the user's choice. Then, on a second
worksheet called "config", there are columns of data indicating the
height and width of "Big", "medium", or "small".

I want to have formulas reference that config data without activating
the worksheet. My thought was to do a find for whatever the user
selected, and then go over to the other columns to pull the data
needed. Here's how it works with activating the sheet:
Dim iHeight As Integer
Dim sSize As String
sSize = Worksheets("Plan").Cells(1, 5).Value
Worksheets("Config").Activate
iHeight = Cells(Cells.Find(What:= sSize, _
After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=
_
False, SearchFormat:=False).Row, 7).Value

Here's how I hoped it would work, but it didn't:
Dim iHeight As Integer
Dim sSize As String
sSize = Worksheets("Plan").Cells(1, 5).Value
iHeight = Worksheets("Config").Cells(Cells.Find(What:= sSize, _
After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=
_
False, SearchFormat:=False).Row, 7).Value

Or, this (which didn't work either):
Dim iHeight As Integer
Dim sSize As String
sSize = Worksheets("Plan").Cells(1, 5).Value
iHeight = Cells(Cells.Find(What:= sSize, _
After:=Worksheets("Config").Cells(1, 1), LookIn:=xlFormulas,
LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=
_
False, SearchFormat:=False).Row, 7).Value

In both cases where it didn't work, the failure only occurred if the
function was triggered from another worksheet. Unfortunately, it needs
to work from other worksheets.

Thanks in advance for any help.

 

[Alan McQuaid via OfficeKB.com]

Hi,

You could try the findit UDF found at
http://www.pcreview.co.uk/forums/thread-3389549.php

With a little modification you could tailor it to suit your requirements.

Alan

 

[p45cal]

try
With Worksheets("Config")
iHeight = .Cells(.Cells.Find(What:=sSize, After:=.Cells(1, 1)
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
7).Value
End Wit