Subscript out of range error

[ub]

Hi
I am getting subscript out of range error in my code.

But if I change the For statement
from
For i = 1 to ubound(a)
to
For i = 1 to 8000
it works.
Ubound(a) has 8266 rows

My Code is:

Dim lastrow As Integer
Dim a As Variant
Dim i As Double
Dim k As Double

Dim ca() As Variant
'break


lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

a = Worksheets("Sheet1").Range("a2", "d" & lastrow).Value
ReDim ca(1 To UBound(a, 1), 1 To 4)

k = 1
i = 1

For i = 1 To UBound(a, 1)

If a(i, 3) = "Y" Then

ca(k, 1) = a(i, 3)


k = k + 1

End If

Next i
Worksheets("Sheet4").Range("b3").Resize(UBound(a, 1), 1).Value = ca


*******
Please advise how can I correct this

 

[Jacob Skaria]

I dont find a reason to return out of range error.

2 things...
Better to declare lastRow as Long or Double
Check whether sheet name exists..

If this post helps click Yes

 

[Jim Thomlinson]

FYI... Don't use double. Doubles can get tiny artifacts added to them such
that a mathemetic operation that should produce a 5 for example might
actually return 5.0000000001. Now your code crashes and it is kind of trick
to find out why....

 

[JLGWhiz]

Maybe I am reading your code incorrectly, but it looks like you have the
variable "a" assigned a value of a range of cells. You then try to use the
Ubound function to find the value of a. To me, it does not compute. Ubound
is normally used to find the upper limit of an array and it does not appear
that "a" equates to an array.

 

[keiji kounoike]

I don't have any problems when i run your code. I tested on Excel 2003
and Ubound(a,1) is 8287 in testing sheet.

Keiji