| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
working on 2 different worksheets....arrays?
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
working on 2 different worksheets....arrays?
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
working on 2 different worksheets....arrays?
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
working on 2 different worksheets....arrays?
"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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
working on 2 different worksheets....arrays?
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
working on 2 different worksheets....arrays?
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
working on 2 different worksheets....arrays?
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Working with arrays as arguments | Excel Worksheet Functions | |||
| SUMPRODUCT with 3 arrays not working | Excel Worksheet Functions | |||
| Working with Arrays | Excel Worksheet Functions | |||
| Arrays, & Worksheets & Grey Hair | Excel Discussion (Misc queries) | |||
| Worksheets and arrays | Excel Programming | |||
Linear Mode
