single dimension array

[RobcPettit]

I have data in a column which I have named dates. What Im doing is using an
array to get those values, then looking for a certain value, stored in 'Dat',
and getting how far down the array that value is. IM using this:
Dim Collectdat() as Variant
Collectdat = Range("Dates").Value
For A = 1 To UBound(Collectdat)
If Dat = Collectdat(A , 1) Then
Row = (A)
Exit For
End If
Next A

What I think Im doing is working with a single dimension array, as Im entering
only one column into the array, therefore I should use '= Collectdat(A), but
this does not work so Ive used the above. Could someone advise were Im going
wrong, Im now thinking that if it was a Row instead of a column this would be
correct.
Also is there a way of seeing how far down my value is without using for/next.
What I meen if the value is the 20 etc, in the array.
Regards Robert

 

[Jim Cone]

Robert,
Your situation sounds tailor made for the WorksheetFunction "Match".
The following works for me...

'Returns the position with the range, returns 0 if no match found.
'Note that the return value is the position in the range, which may
'not be the same as the row number.
'------------------------------
Sub FindThePosition()
Dim MyValue As String
Dim MyRange As Range
Dim Rw As Long

MyValue = "NeverUpGrade"
Set MyRange = Columns(2)
On Error Resume Next
Rw = Application.Match(MyValue, MyRange, 0)
On Error GoTo 0
MsgBox Rw
End Sub

 

[Alan Beban]

Dim Collectdat() as Variant
Collectdat = Range("Dates").Value
iRow = Application.Match(Dat, Collectdat, 0)

Any array loaded directly from a range, even if a single column or
single row, is 2-dimensional.

I used iRow as the variable name to avoid using a variable name with an
assigned meaning in Excel, i.e., Row. That sometimes causes problems as
well as confusion.

Alan Beban

 

[Robert Pettit]

Thankyou both for replys, both have been helpful. Thanks alan for
explaining about the array bieng 2 dimensional, now to use it in my
program. Thankyou
Regards Robert