Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range error
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 --------------- Jacob Skaria "ub" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range error
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.... -- HTH... Jim Thomlinson "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "ub" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range error
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. "ub" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range error
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 ub wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subscript out of range error? | Excel Programming | |||
Runtime Error - Subscript out of range despite On Error statement | Excel Programming | |||
Subscript out of range error - save copy error | Excel Programming | |||
Subscript out of range error - save copy error | Excel Programming | |||
Type Mismatch error & subscript out of range error | Excel Programming |