hi keith

i wasnt able to work out with arrays here...thanks for the code below...
it clearly uses the 2 workbooks separately...

thanks again
monika

"Keith Willshaw" wrote in message
...

"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....



You could do either BUT switching between workbooks is relatively slow
IMHO you'd do far better to put the input values in an array

Assuming you run the code from the sheet containg the input data

Dim LastCellNum as Long
Dim myarray() as string

LastCellnum=800

Redim myarray(1,lastCellNum) as string

Dim MyWksht as Worksheet
Set Mywksht = ActiveSheet


With Mywksht
For i=2 to lastCellNum
myarray(0,i) = .Cells(i,3).value
myarray(1,i) = ""
Next i

End With

' You now have a 2 dimensional array with your search string
' in Subs (0,n) and a Blank Value in (1,n) i which to store your results

'Switch to the worksheet you want to search

Workbooks("PackageMasterFile").Worksheets("Package ").Activate

'To Look in Column A

Dim FindRange as Range , FoundRange as Range

Set FindRange = ActiveSheet.Columns(1)

For i=2 to lastCellNum

Set FoundRange = FindRange.Find(myarray(0,i), LookIn:=xlValues)

' If find was succesful the range of the cell is in
' Foundrange,if not FoundRange is nothing

If Not FoundRange Is Nothing Then
myarray(1,i)=FoundRange.Value
Else
myarray(1,i)="Not Found"
End If

' Reinitialise for next trip around
Set FoundRange = Nothing

Next i

When thus has finished you have an array with your search criteria and
any matches

Keith