working on 2 different worksheets....arrays?

  • Thread starter Thread starter monika
  • Start date Start date
M

monika

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
 
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.
 
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..
 
monika said:
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....
Click to expand...


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
 
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
 
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.
 
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
 
Back
Top