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

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" <lif.2a1gen_1151374204.2881@excelforum-nospam.com> wrote in message news:lif.2a1gen_1151374204.2881@excelforum-nospam.com...
>
> 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
>

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