lforster9999
New Member
- Joined
- Sep 16, 2020
- Messages
- 7
- Office Version
- 365
- Platform
- Windows
- MacOS
Hi, I am trying to pass data from an Array to certain sheets, however passing the values to the sheets does not seem to work and results in an error of "Run Time Error 9: Subscript Out of Range"
The data is stored in the array as shown by the screenshot of the local window below. Then when resizing the range to be filled, it errors out. I think the Array may be saved as a 3D array rather than a 2D one (just guessing) .
Code to populate the sheet is as follows.
The code is being called as follows.
Private details have been scribbled out in photo.
Any help is greatly appreciated as it is really annoying me!
The data is stored in the array as shown by the screenshot of the local window below. Then when resizing the range to be filled, it errors out. I think the Array may be saved as a 3D array rather than a 2D one (just guessing) .
Code to populate the sheet is as follows.
VBA Code:
Sub InsertArrayToSheet(ArrayName, DestinationSheetName)
Dim ArrLastRow As Long: ArrLastRow = UBound(ArrayName) - LBound(ArrayName)
Dim DestinationRange As Range
'Set DestinationRange = Range("A1:AO" & (ArrLastRow))
Sheets(DestinationSheetName).Range("A1").Resize(UBound(ArrayName, 1) + 1, UBound(ArrayName, 2) + 1).Value = ArrayName
End Sub
The code is being called as follows.
VBA Code:
'Fill PQL, 1ITW, 2ITW, PPL sheet
Call InsertArrayToSheet(PQLArray, "PQL")
Private details have been scribbled out in photo.
Any help is greatly appreciated as it is really annoying me!