I am populating an array from a row of a table. I am able to print the entire array, but I am not able to print a member or iterate the array independently as shown in the following images:
Image 1) the array is printed
Image 2) the subscript is out of range
As also seen on the image here is the code:
Sub try_w()
Dim H_PR() As Variant
H_PR_dim = Worksheets("COV_CHOL").ListObjects("Historical_PR").ListRows(1).Range.Count
ReDim H_PR(H_PR_dim)
H_PR = Worksheets("COV_CHOL").ListObjects("Historical_PR").ListRows(1).Range.Value
'1.a.2)
'Try
Worksheets("H_PRi").Range("B2:E2") = H_PR
'Try end/PASS
'Try
Worksheets("H_PRi").Range("B3") = H_PR(1)
'Try end/FAIL
End Sub
Option Explicit
Sub try_w()
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
' Source
Dim sws As Worksheet: Set sws = wb.Worksheets("COV_CHOL")
Dim stbl As ListObject: Set stbl = sws.ListObjects("Historical_PR")
' Range
' This is a one-row range.
Dim srrg As Range: Set srrg = stbl.ListRows(1).Range
' Number of columns
'Dim cCount As Long: cCount = srrg.Columns.Count
' Number of rows
'Dim rCount As Long: rCount = srrg.Rows.Count
' Array
' You can write the data from a range using the following line.
' You don't need the number of rows or columns.
Dim srData As Variant: srData = srrg.Value
' Note that this works only if there are at least two cells in the range!
' See the function below on how to do it properly.
' Number of columns
Dim cCount As Long: cCount = UBound(srData, 2)
' Number of rows
'Dim rCount As Long: rCount = UBound(srData, 1)
' Destination
Dim dws As Worksheet: Set dws = wb.Worksheets("H_PRi")
'1.a.2)
'Try
dws.Range("B2").Resize(, cCount).Value = srData
' or
'dws.Range("B2").Resize(1, cCount).Value = srData
' or without cCount:
'dws.Range("B2").Resize(, UBound(Data, 2)).Value = srData
' Note that the default of both 'Resize' arguments is '1' i.e.
' Range("B2").Resize(1, 1) is Range("B2"), while
' Range("B2").Offset(0, 0) is Range("B2").
'Try end/PASS
'Try
dws.Range("B3").Value = srData(1, 1)
dws.Range("B4").Value = srData(1, 2)
dws.Range("B5").Value = srData(1, 3)
dws.Range("B6").Value = srData(1, 4)
'Try end/FAIL
' Note that this is just practice. The array is redundant here
' because you can simply do...
' dws.Range("B2").Resize(, cCount).Value = srrg.Value
' dws.Range("B3").Value = srrg.Cells(1)
' dws.Range("B4").Value = srrg.Cells(2)
' dws.Range("B5").Value = srrg.Cells(3)
' dws.Range("B6").Value = srrg.Cells(4)
' which is called 'copying by assignment'.
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose: Returns the values of a range in a 2D one-based array.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function GetRange( _
ByVal rg As Range) _
As Variant
If rg Is Nothing Then Exit Function
Dim rData As Variant
If rg.Rows.Count + rg.Columns.Count = 2 Then
ReDim rData(1 To 1, 1 To 1): rData(1, 1) = rg.Value
Else
rData = rg.Value
End If
GetRange = rData
End Function
' In our previous example, we can utilize the function like this:
'Dim srData As Variant: srData = GetRange(srrg)