CODEWITHSUNDEEP
arraysexcelvbarange
Pf256
Pf256

Reputation: 1

Subscript out of range, moving data from range to array

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

Upvotes: 0

Views: 59

Answers (3)

ARL
ARL

Reputation: 64

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.

Upvotes: 2

JohnyL
JohnyL

Reputation: 7142

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

Upvotes: 1

Error 1004
Error 1004

Reputation: 8220

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.

Upvotes: 0

Related Questions