hi..

I have to select a value from one sheet and find this value
in another
sheet. The problem is ... there are some 800 such values. I
am selecting a
value from sheet and then activating the other sheet again.
off course this
is not a correct way...as the sheets keep blinking! I think
i need to use
arrays....but still i am unable to figure out the exact
methood...

here is my code:
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

any help?

thanks
monika

Does this speed things up ?

Dim pmf As Worksheet
Dim brd As Worksheet


Set pmf = Workbooks("PackageMasterFile").Worksheets("Package ")
Set brd = Workbooks("best").Worksheets("raw_data")
Application.ScreenUpdating = False
i = 2
While i <= lastCellNum
fam = check_fam(Cells(i, 3))
'RETRIEVE FINAL PACKAGE FAMILY FROM
"PackageMasterFile.xls"


Set Ofind = mf.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)
brd.Cells(i, 4) = extractValue
End If
i = i + 1
Wend
Application.ScreenUpdating = Tru

--
Message posted from http://www.ExcelForum.com

Hi Kieran

thanks for the response.

Yes my blinking problem resolved with screenupdating ... i never realized
its usage clearly..though i had been using it previously ..thanks...!

but still the whole process is taking time... because i am activating both
files one by one for 800 such values.

thanks
Monika


"Kieran " wrote in message
...
Does this speed things up ?

Dim pmf As Worksheet
Dim brd As Worksheet


Set pmf = Workbooks("PackageMasterFile").Worksheets("Package ")
Set brd = Workbooks("best").Worksheets("raw_data")
Application.ScreenUpdating = False
i = 2
While i <= lastCellNum
fam = check_fam(Cells(i, 3))
'RETRIEVE FINAL PACKAGE FAMILY FROM
"PackageMasterFile.xls"


Set Ofind = mf.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)
brd.Cells(i, 4) = extractValue
End If
i = i + 1
Wend
Application.ScreenUpdating = True


---
Message posted from http://www.ExcelForum.com/