Subscript Out Of Range Error?

[Michael Vaughan]

Hello Everyone,

Can somebody tell me why this gets a Subscript out of range error??? It
errors out at the vArray(I) at the bottom.

Sub findLastCell_Without_Scrolling()
Dim lLastrow As Long, vArray As Variant
Dim scrollCol As Integer, scrollRow As Integer
Dim I As Integer
'store the scroll settings
scrollCol = ActiveWindow.ScrollColumn
scrollRow = ActiveWindow.scrollRow
'set a range variable to the first blank
lLastrow = Range("A1").End(xlDown).Row
'apply the stored scroll settings

ActiveWindow.ScrollColumn = scrollCol
ActiveWindow.scrollRow = scrollRow
'display the cell found
'MsgBox firstBlank.Address

vArray = Range("A1:A" & lLastrow).Value

For I = 1 To lLastrow
If vArray(I) = "11/10/2004" Then MsgBox "The Date is " & I: Exit For
Next I

'write results out to a column range
Range("G5:G" & lLastrow).Value = vArray

End Sub

 

[crispbd]

Need to dimension the array
Dim vArray(100) as Varian

 

[Tom Ogilvy]

That won't fix the problem. the problem is that an array picked up in this
fashion is two dimensional

If vArray(I,1) = "11/10/2004" Then MsgBox "The Date is " & I: Exit For

should work.

however, you pick up from 1 to Lastrow and write out to 5 to lastrow.
Should it be 5 to lastrow + 4

 

[Michael Vaughan]

Thanks Tom,

It worked perfectly. As far as the "5 to lastrow + 4", I am just playing
around with the arrays to be able to look up data that I put into an array.
I can use the formulas array function just fine with vlookup, but I wanted
to be able to do it with VBA. Actually, what I really want to do is load my
info into an array. Then search for a paticular date and then use the
celloffset to look up a name that is matched with that date. Once I get
that accomplished, then I will be able to setup a userform that will tell me
which bills are due and which bills have an auto debit. All kindergarten
stuff to you, but a learning experience for me. I am slowly getting there
with all your wonderful help. I have got 10 books, and bought the Visual
Basic Macros/Samples Made Easy. It helps a lot, but still can't get all the
info that I need. Thanks again for your help!! I only wish I knew half of
what you know about Excel VBA.
mv