i tried keith's code... i dont need to define double dimensional array ...
but it gave me an idea how to loop where...

somehow i found it faster (maybe phychologically) .. .but its first taking
in all the values in an array adn then opens the other book and check the
array ..and makes another range of cells with final values...so ther eis no
switchign to and fro... even if we use screen updating then also it was
takign time

reg
monika
"Tom Ogilvy" wrote in message
...
No it doesn't switch back at all. Turn screen updating on and you will
see
no switching.

I doubt Keith's solution would perform much differently since he is
looping
to fill his array (which would be the slowest way to do it). Since you
can't get it to work, there isn't much use offering anything else.

--
Regards,
Tom Ogilvy

"monika" wrote in message
...
hi Tom,

thanks for the response.....

wudnt this again will switch from one sheet to another...

lastCellNum is the total no. of filled cells in best worksheet...so i
have
to perform the loop that many times. check_fam is a function where i am
extracting the family from 10 lettered ID. so for each cell it goes to
the
check_fam and then wudnt it go back each time to the
PackageMasterFile???
(the sh2) sheet here?

like thre are 800 packages...which has only 10 families....

all this families are stored in sh2 and i have to find out the family
for
each 800 packages..

reg

Monika..
"Tom Ogilvy" wrote in message
...
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