Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find each of the items in an array and save result in another array
Hi, There must be an easy solution to this. I am working with an array of values and need to look up each value in a worksheet and store the value of a cell 3 columns away in another array. Now the following code works for single variable Dim Pre as string With Worksheets("Pre_Rawdata").Range("h2:p8") Pre = .Find(What:="13", After:=.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3) End With but I actually need to do the same thing but where pre is an array and the What:= term is an array. like this Dim Pre(1 to 25) as string Dim precondtition(1 to 25) as string For i = 1 To 25 With Worksheets("Pre_Rawdata").Range("h2:p8") Pre(i) = .Find(What:=precondition(i), After:=.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3) End With next I get "Object variable or with block variable not set" How can I find all the members of an array in a range and store the results in another array??? Thanks for any help, Luis -- lif ------------------------------------------------------------------------ lif's Profile: http://www.excelforum.com/member.php...o&userid=35745 View this thread: http://www.excelforum.com/showthread...hreadid=555836 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find each of the items in an array and save result in another array
If .Find doesn't find a match then it returns Nothing instead of a Range object
Try dim r as Range ..... set r = .Find(What:="13", After:=.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) if not r is nothing then Pre(i) = r.Offset(0, 3).value else Pre(i) = "" 'or whatever indicates "not found" end if Tim "lif" wrote in message ... Hi, There must be an easy solution to this. I am working with an array of values and need to look up each value in a worksheet and store the value of a cell 3 columns away in another array. Now the following code works for single variable Dim Pre as string With Worksheets("Pre_Rawdata").Range("h2:p8") Pre = .Find(What:="13", After:=.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3) End With but I actually need to do the same thing but where pre is an array and the What:= term is an array. like this Dim Pre(1 to 25) as string Dim precondtition(1 to 25) as string For i = 1 To 25 With Worksheets("Pre_Rawdata").Range("h2:p8") Pre(i) = .Find(What:=precondition(i), After:=.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3) End With next I get "Object variable or with block variable not set" How can I find all the members of an array in a range and store the results in another array??? Thanks for any help, Luis -- lif ------------------------------------------------------------------------ lif's Profile: http://www.excelforum.com/member.php...o&userid=35745 View this thread: http://www.excelforum.com/showthread...hreadid=555836 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find each of the items in an array and save result in another array
Thanks Tim, This is precisely what I needed - The find seems to be working. I still have a problem getting the results I'm after - though. If anyone has some imput it would be greatly appreciated. I'm using several listboxes to get input from the user. The contents of the listboxes is the same and the first choice is selected by default. Here's the code for that - all listboxes contain the same data from a worksheet range - they just start at a different initial value (I got help from MaC from this forum on this chunk!!): Dim cbCtl As Control For Each cbCtl In F5DataSummary.Controls If TypeName(cbCtl) = "ListBox" And Left(cbCtl.Name, 3) = "Pre" Then For i = 1 To 2 + totalwaferspre cbCtl.AddItem Worksheets("Pre_Rawdata").Range("h" & i + Right(cbCtl.Name, 1)).Value Next cbCtl.ListIndex = 0 ElseIf TypeName(cbCtl) = "ListBox" And Left(cbCtl.Name, 3) = "Pos" Then For i = 1 To 2 + totalwaferspre cbCtl.AddItem Worksheets("Post_Rawdata").Range("h" & i + Right(cbCtl.Name, 1)).Value Next cbCtl.ListIndex = 0 End If Next Then I want to use the input to find something else. I was using the precondition(i) to record the data dim r as Range dim i as integer dim lbctl as control For i = 1 To 25 For Each lbctl In F5DataSummary.Controls If TypeName(lbctl) = "ListBox" Then If lbctl.Name Like "Pre" & i Then precondition(i) = lbctl.Value Debug.Print precondition(i) set r = .Find(What:=precondition(i), After:=.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) if not r is nothing then Pre(i) = r.Offset(0, 3).value else Pre(i) = "" 'or whatever indicates "not found" end if End If End If Next Next But for some reason - precondition(i) = lbctl.value is only found for 2 out of the 4 listboxes even though all four list boxes have valid data. i.e. the actual values that I see in the form that I've selected should be listbox1.value = 1 listbox2.value = 2 listbox3.value = 4 listbox4.value = 5 but the when I do debug.print the values are listed as listbox1.value = 1 listbox2.value = blank listbox3.value = blank listbox4.value = 5 Why is this the case? Why are the values of listbox2 and listbox3 seen as empty when there is clearly something there??? Thanks again for any help, Luis -- lif ------------------------------------------------------------------------ lif's Profile: http://www.excelforum.com/member.php...o&userid=35745 View this thread: http://www.excelforum.com/showthread...hreadid=555836 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find a result in an array in the same row as a minimum resu | Excel Discussion (Misc queries) | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Use FIND to return an array of items | Excel Programming | |||
Alphabetizing array items | Excel Programming |