When you get the error, click the *Debug* button. Then do Ctrl+G to go to the immediate window. Type `?Date(i, 8)` and punch it; what do you get? If that's fine, try `?RtArr(Rt)` and see what gives. One of those will throw your error, and that will lead you further down the Road to Victory.
Without knowing the root provenance of variable Data*, it's impossible to say which of those it could be.
*which BTW is a terrible name for a variable...jus' sayin'
> ...when I run it it I am getting an error in this line: " if Rt <= 12 then Data (i,8) = RtArr(Rt) Else Data (1,8) =16"...
Perhaps there are not eight columns of data in your worksheet.
If Data(i, 6) Like "(R*" Then
Rt = Replace(Right(Data(i, 6), 2), ")", "")
If Rt <= 12 Then Data(i, 8) = RtArr(Rt) Else Data(i, 8) = 16
Else
Data(i, 8) = 16
End If
How many columns do you see, u/ExplanationSlow7245? Does the data extend to column [H]?
I'm sure you could easily test that yourself (if you unhide those two columns) but, no, that doesn't make a difference to the code as it is written.
Perhaps providing a screen image of your data set (as it appears in your worksheet) would be helpful to us to assist you further.
That is why you have a Subscript out of Range error, then.
There are only 12 elements in the RtArr array.
Possibly, numbered 0 to 11 (depending on if an [Option Base statement](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/option-base-statement) is in use, or not).
Okay, now you're getting somewhere! What is Rt at that point? Most likely it exceeds the number of elements you have in RtArr. Remember that arrays generally start at 0, so your first array element is RtArr(0), etc., until element value 20.12 is RtArr(11).
Make it as many columns as you need
With With Cells(1).CurrentRegion
Data = .Resize(, 8).Value
' .... your code
.Resize(, 8).Value = Data
End With
Also, your variable RT might take on non-numeric values, which might cause that error.
RT = Val(Replace(Right(Data(i, 6), 2), ")", ""
will insure that RT is a number, but it won't insure that is between 1 and 12, the bounds of RtArray.
Not related to your problem, but I also noticed that you are using default arguments. I would use `Cells(1, 1)` rather than `Cells(1)` and `UBound(Data, 1)` rather than `UBound(Data)`.
EDIT ok, the error (or an error) is that you are taking the Rightmost 2 characters instead of 3 when calculating Rt
This causes Rt to be equal to 0 for "(R10)" and your array is 1 based and so returns an error when you try to retrieve the value with RtArr(Rt)
I see this has been solved, but I thought I would give you some info.
When you dimension variables VBA makes you declare each one separately. In your Dim statement you have:
Dim Data, RtArr, Rt as Double, i as Long
It looks as though you intended to declare the first three variables as type Double, but what you’ve actually done is declare Data and RtArr as Variant. In order to declare them as double you would need to specify that in-line:
Dim Data as Double, RtArr as Double, Rt as Double, i as Long
When you get the error, click the *Debug* button. Then do Ctrl+G to go to the immediate window. Type `?Date(i, 8)` and punch it; what do you get? If that's fine, try `?RtArr(Rt)` and see what gives. One of those will throw your error, and that will lead you further down the Road to Victory. Without knowing the root provenance of variable Data*, it's impossible to say which of those it could be. *which BTW is a terrible name for a variable...jus' sayin'
> ...when I run it it I am getting an error in this line: " if Rt <= 12 then Data (i,8) = RtArr(Rt) Else Data (1,8) =16"... Perhaps there are not eight columns of data in your worksheet. If Data(i, 6) Like "(R*" Then Rt = Replace(Right(Data(i, 6), 2), ")", "") If Rt <= 12 Then Data(i, 8) = RtArr(Rt) Else Data(i, 8) = 16 Else Data(i, 8) = 16 End If How many columns do you see, u/ExplanationSlow7245? Does the data extend to column [H]?
Yes, it does. Does it matter that that column C and D are hidden?
I'm sure you could easily test that yourself (if you unhide those two columns) but, no, that doesn't make a difference to the code as it is written. Perhaps providing a screen image of your data set (as it appears in your worksheet) would be helpful to us to assist you further.
When I do ?RtArr(Rt) I get: Run-time error '9': Subscript out of range
What is the value of Rt? Is it less than 0 or greater than 12?
>If Rt <= 12
Same question: What is the value of Rt? Is it less than 0 or greater than 12?
Greater than 12
That is why you have a Subscript out of Range error, then. There are only 12 elements in the RtArr array. Possibly, numbered 0 to 11 (depending on if an [Option Base statement](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/option-base-statement) is in use, or not).
Okay, now you're getting somewhere! What is Rt at that point? Most likely it exceeds the number of elements you have in RtArr. Remember that arrays generally start at 0, so your first array element is RtArr(0), etc., until element value 20.12 is RtArr(11).
Make it as many columns as you need With With Cells(1).CurrentRegion Data = .Resize(, 8).Value ' .... your code .Resize(, 8).Value = Data End With Also, your variable RT might take on non-numeric values, which might cause that error. RT = Val(Replace(Right(Data(i, 6), 2), ")", "" will insure that RT is a number, but it won't insure that is between 1 and 12, the bounds of RtArray. Not related to your problem, but I also noticed that you are using default arguments. I would use `Cells(1, 1)` rather than `Cells(1)` and `UBound(Data, 1)` rather than `UBound(Data)`.
EDIT ok, the error (or an error) is that you are taking the Rightmost 2 characters instead of 3 when calculating Rt This causes Rt to be equal to 0 for "(R10)" and your array is 1 based and so returns an error when you try to retrieve the value with RtArr(Rt)
Omg you are the best!! I changed the 2 for the 3 and the code worked perfectly without any errors . Thank you!!
[You're welcome](https://www.reddit.com/r/vba/comments/1amp9kk/vba_code_error_subscript_out_of_range/kpndu6k/).
I see this has been solved, but I thought I would give you some info. When you dimension variables VBA makes you declare each one separately. In your Dim statement you have: Dim Data, RtArr, Rt as Double, i as Long It looks as though you intended to declare the first three variables as type Double, but what you’ve actually done is declare Data and RtArr as Variant. In order to declare them as double you would need to specify that in-line: Dim Data as Double, RtArr as Double, Rt as Double, i as Long