Subscript out of range, moving data from range to array

Question

I am completely new to programming here.

I am getting a subscript out of range error on the following code:

b(i) = ThisWorkbook.Sheets("trades").Range("AA & arxi +i-1:AD& arxi+i-1").Value

I believe it has to do with the syntax, or maybe because I haven't redimed b(vdomada,4) ?

In any case the data from the range do not go into the array. The result would be to input the data in the array and then export them in a different excel file(the exporting bit is not into the code provided)

Thanks in advance for your help.

Sub report()
Dim b As Variant, arxi As Integer, telos As Variant
Dim LastDayRow As Integer, vdomada As Integer, i As Integer, z As Integer
Dim LastDay As Date


Application.Workbooks("Back Office 2019.xlsx").Activate

Sheets("trades").Select
LastDayRow = Range("L" & Rows.Count).End(xlUp).Row
telos = Cells(LastDayRow, 12)
arxi = InputBox("Please enter the row for the start of the week")
vdomada = LastDayRow - arxi
ReDim b(vdomada) As Variant
For i = 1 To vdomada

b(i) = ThisWorkbook.Sheets("trades").Range("AA & arxi +i-1:AD& arxi+i-1").Value
Next i

End Sub

Change the line to this b(i) = ThisWorkbook.Sheets("UST trades").Range("AA" & arxi + i - 1 & ":AD" & arxi + i - 1).Value — Mikku
– Mikku, Commented Jun 17, 2019 at 15:22

ARL · Accepted Answer · 2019-06-18 15:14:12Z

2

Few things that might help. I would remove the following-

ReDim b(vdomada) As Variant
For i = 1 To vdomada

b(i) = ThisWorkbook.Sheets("trades").Range("AA & arxi +i-1:AD& arxi+i-1").Value
Next i

And replace it with-

b = ThisWorkbook.Sheets("trades").Range("AA" & arxi & ":AD" & vdomada).Value

See if that helps.

I think your error originates from giving Range something it doesn't know what to do with and from trying to assign a 2D range to an array that's been defined as 1D. I don't think you need to ReDim the array in this case.

Good luck.

edited Jun 18, 2019 at 15:14

answered Jun 17, 2019 at 15:21

ARL

749 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JohnyL · Accepted Answer · 2019-06-17 17:28:51Z

1

With ThisWorkbook.Sheets("UST trades")
    b(i) = .Range(.Cells(arxi + i - 1, "AA"), .Cells(arxi + i - 1, "AD")).Value
End With

edited Jun 17, 2019 at 17:28

answered Jun 17, 2019 at 17:19

JohnyL

7,1945 gold badges27 silver badges48 bronze badges

Comments

Error 1004 · Accepted Answer · 2019-06-17 15:59:20Z

0

How to avoid .Activate

With Workbooks("Back Office 2019.xlsx").Sheets("trades")
    LastDayRow = .Range("L" & Rows.Count).End(xlUp).Row
    telos = .Cells(LastDayRow, 12)
    arxi = InputBox("Please enter the row for the start of the week")
    vdomada = LastDayRow - arxi
End With

Additionally, variables which takes whole numbers( NOT decimals) is better to declare As Long. Longs can take higher bigger numbers.

edited Jun 17, 2019 at 15:59

answered Jun 17, 2019 at 15:49

Error 1004

8,2863 gold badges36 silver badges58 bronze badges

Collectives™ on Stack Overflow

Subscript out of range, moving data from range to array

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related