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

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
------------------------------------

Regards,
Jim Cone
San Francisco, Ca

"RobcPettit" wrote in message
...
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

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

RobcPettit wrote:
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

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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!