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

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

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