How do i check if an items value lies within an array of values? it is a
variable integer array.

Dim res as Variant
res = application.Match(MyVal,My1DArray,0)
if not iserror(res) then
msgbox MyVal & " was fund"
End if

When working with arrays, Match is limited to around 5000 elements in the
array in older versions of excel.

in xl2003, this worked, however:

Sub abcd()
Dim v(1 To 65536) As Long
Dim res As Variant
MyVal = 6
v(65536) = MyVal
res = Application.Match(MyVal, v, 0)
If Not IsError(res) Then
msgbox MyVal & " was found at " & res
End If
End Sub
--
Regards,
Tom Ogilvy


"Rbp9ad" wrote in message
...
How do i check if an items value lies within an array of values? it is a
variable integer array.

I wrote a macro that sorts records in a workbook based on criteria. The name
of who it to be sent to and whether they have approved the record. When I
used a column in the workbook to record whether it had been sent it worked
fine. It did not make sense to keep a record of this in the workbook. I
tried to rewrite the code to where it keeps track of this in an array, but
have not been able to. Could you help me find where i went wrong?

Sub sendem()
Dim rng As Object
Dim fndrow As Range
Dim counter As Integer
Dim myadd As Variant
Dim newbk As Workbook
Dim d As String
Dim ar() As Long
Dim res As Variant
Dim already As Boolean

Application.ScreenUpdating = False

Set rng = Workbooks("mail.xls").Worksheets(1).Range("a1:a100 0")
'range of record #
For Each Item In rng
already = False
On Error Resume Next
'check if record was previously pasted
res = Application.Match(Item.Value, ar, 0)
If Not IsError(res) Then
already = True
End If

If Item.Value < "" And Item.Offset(0, 3).Value = "" And already = False
Then
Set newbk = Workbooks.Add
d = usrnm(Item.Offset(0, 10).Value) & " " & Format(Date, "mm-dd-yyyy")
'takes a name from the list and converts it to the username
'convention used by my network and puts the date at the end
newbk.SaveAs Filename:="C:\Documents and Settings\rpatterson\My
Documents\" & d & ".xls"
Set fndrow = Workbooks("mail.xls").Worksheets(1) _
.Range("k1:k100").Find(Item.Offset(0, 10), LookIn:=xlValues)

If Not fndrow Is Nothing Then
myadd = fndrow.Address
counter = 1
Do
If fndrow.Offset(0, -8).Value = "" Then
'add record# to array of already pasted values
On Error Resume Next
ub = UBound(ar)
If Err.Number < 0 Then
ReDim ar(0 To 0)
ar(UBound(ar)) = fndrow.Value
Else
ReDim Preserve ar(UBound(ar) + 1)
ar(UBound(ar)) = fndrow.Value
End If
Workbooks("mail.xls").Worksheets(1).Range(fndrow.O ffset(0, -10),
fndrow.Offset(0, 2)).Copy
Workbooks(d & ".xls").Activate
ActiveSheet.Paste Destination:=Worksheets(1).Range("a" & counter)
counter = counter + 1
'paste record to appropriate file
Workbooks("mail.xls").Activate
fndrow.Offset(0, -8).Value = Date
End If
Set fndrow = Workbooks("mail.xls").Worksheets(1) _
.Range("k1:k100").FindNext(fndrow)
Loop While Not fndrow Is Nothing And fndrow.Address < myadd
Workbooks(d & ".xls").Save
Workbooks(d & ".xls").Close
End If
End If
Workbooks("mail.xls").Activate
Next Item
Application.ScreenUpdating = True
End Sub
"Tom Ogilvy" wrote in message
...
Dim res as Variant
res = application.Match(MyVal,My1DArray,0)
if not iserror(res) then
msgbox MyVal & " was fund"
End if

When working with arrays, Match is limited to around 5000 elements in the
array in older versions of excel.

in xl2003, this worked, however:

Sub abcd()
Dim v(1 To 65536) As Long
Dim res As Variant
MyVal = 6
v(65536) = MyVal
res = Application.Match(MyVal, v, 0)
If Not IsError(res) Then
msgbox MyVal & " was found at " & res
End If
End Sub
--
Regards,
Tom Ogilvy


"Rbp9ad" wrote in message
...
How do i check if an items value lies within an array of values? it is a
variable integer array.