Subscript Out Of Range Error?
 

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

Subscript Out Of Range Error?
 

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

--
crispb
-----------------------------------------------------------------------
crispbd's Profile: http://www.excelforum.com/member.php...fo&userid=1088
View this thread: http://www.excelforum.com/showthread.php?threadid=27641

Subscript Out Of Range Error?
 

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

--
Regards,
Tom Ogilvy

"crispbd" wrote in message
...

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


--
crispbd
------------------------------------------------------------------------
crispbd's Profile:
http://www.excelforum.com/member.php...o&userid=10880
View this thread: http://www.excelforum.com/showthread...hreadid=276412

Subscript Out Of Range Error?
 

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

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

--
Regards,
Tom Ogilvy

"crispbd" wrote in message
...

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


--
crispbd
------------------------------------------------------------------------
crispbd's Profile:
http://www.excelforum.com/member.php...o&userid=10880
View this thread:
http://www.excelforum.com/showthread...hreadid=276412