wsnyder
Board Regular
- Joined
- Sep 23, 2018
- Messages
- 223
- Office Version
- 365
- Platform
- Windows
Hi All,
Using Excel 365.
I'm getting a Type Mismatch error when trying to print the content of an Array I created by passing a Range to the Array
Range Values:
R4C1 = BB
R4C2 = 34
In my test. I do not know the size of the Range. It is coming from a Pivot Table that may change through each iteration.
Error msg:
Here:
What am I doing wrong?
Thanks,
-w
Full code (Test) :
Using Excel 365.
I'm getting a Type Mismatch error when trying to print the content of an Array I created by passing a Range to the Array
Range Values:
R4C1 = BB
R4C2 = 34
In my test. I do not know the size of the Range. It is coming from a Pivot Table that may change through each iteration.
Error msg:
Run-time error '13':
Type mismatch
Here:
VBA Code:
Debug.Print i, j, arr(i, j)
What am I doing wrong?
Thanks,
-w
Full code (Test) :
Code:
Option Explicit
Sub GetDataFromPivot()
Dim wb As Workbook
Dim ws As Worksheet
Dim pt As PivotTable
Dim rng As Range
Dim r As Long
Dim c As Long
Dim arr As Variant
Dim i As Long
Dim j As Long
Set wb = ThisWorkbook
Set ws = wb.Worksheets("pvt")
With ws
For Each pt In .PivotTables
Set rng = pt.RowRange
Debug.Print "1: ", rng.Address
With rng
r = .Rows.Count
c = .Columns.Count
End With
Set rng = rng.Resize(r - 2, c + 1).Offset(1, 0)
Debug.Print "2: ", rng.Address
With rng
r = .Rows.Count
c = .Columns.Count
End With
Debug.Print "rows: ", r
Debug.Print "Cols: ", c
Next pt
End With
If rng.Rows.Count = 1 Then
ReDim arr(1 To 1, 1 To 2)
arr(1, 1) = rng.Value
Else
arr = rng.Value
End If
Debug.Print "=============="
Debug.Print "Array data"
Debug.Print "i", "j", "Value"
For i = LBound(arr) To UBound(arr)
For j = LBound(arr, 2) To UBound(arr, 2)
Debug.Print i, j, arr(i, j)
Next j
Next i
End Sub