application.screenupdating = false
i = 2
sh1 = Workbooks("best").Worksheets("raw_data")
sh2 = Workbooks("PackageMasterFile").Worksheets("Package ")

While i <= lastCellNum
fam = check_fam(sh1.Cells(i, 3))
'RETRIEVE FINAL PACKAGE FAMILY FROM "PackageMasterFile.xls"

Set Ofind = sh2.Columns("A:A").Find(fam)
If Ofind Is Nothing Then
'MsgBox (fam)
MsgBox "The particular Family not found in
PackageMasterFile.xls"
Exit Sub
Else
'MsgBox (Ofind.Address)
extractValue = Ofind.Offset(0, 1)
sh1.Cells(i, 4) = extractValue
End If
i = i + 1
Wend
application.screenupdating = true

I don't know if I got the right sheets associate with your actions, but you
should be able to adjust which sheet using this method.

This avoids switching back and forth.

--
Regards,
Tom Ogilvy

"monika" wrote in message
...
sorry to be askign this question again...

I want to trap a range of values in a variable, something like set
InputRange = Columns(1).rows from workheet best.xls.

then i want to search each value in this Range to find a value assigned to
this individual value stored in another sheet PackageMasterFile.xls

Currently What is happening is I am switching between 2 workbooks one cel
by cell.... which is taking a lot time... bec InputRange may contain some
800 values. so what i intend to do is...store the inputRange as a public
variable and then check each value in the other workbook to locate its
assigned value.

I am getting confused as to whether i need to use arrays or looping....

please advise

here is my code:
application.screenupdating = false
i = 2
While i <= lastCellNum
fam = check_fam(Cells(i, 3))
'RETRIEVE FINAL PACKAGE FAMILY FROM "PackageMasterFile.xls"

Workbooks("PackageMasterFile").Worksheets("Package ").Activate
Set Ofind = Columns("A:A").Find(fam)
If Ofind Is Nothing Then
'MsgBox (fam)
MsgBox "The particular Family not found in
PackageMasterFile.xls"
Exit Sub
Else
'MsgBox (Ofind.Address)
extractValue = Ofind.Offset(0, 1)
Workbooks("best").Worksheets("raw_data").Activate
Cells(i, 4) = extractValue
End If
i = i + 1
Wend
application.screenupdating = true

thanks a lot...
monika