Range To Array Type Mismatch

[wsnyder]

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:

Run-time error '13':
Type mismatch

Here:

Debug.Print i, j, arr(i, j)

What am I doing wrong?

Thanks,
-w

Full code (Test) :

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

 

[bobsan42]

I suspect that for some reason your arr has not succeeded to become an array and is still a variant.
Check it in the Locals Window at the time of the error.

 

[Akuini]

I don't fully understand the code but try replacing this:

    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

with:

    If Rng.Cells.Count = 1 Then
        ReDim arr(1 To 1, 1 To 1)
        arr(1, 1) = Rng.Value
    Else
        arr = Rng.Value
    End If

 

[wsnyder]

Thanks Akuini,

I'll give it a try, but the second dimension seems odd.
There can be any number of Rows in the Pivot Table
But there will always be 2 columns
That's why I thought 1 To 2 for the 2 Dimension.

Thanks
-w

 

[Akuini]

But there will always be 2 columns

In that case there’s no possibility that Rng is a SINGLE CELL, so actually you only need 1 line, so replace this:

    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

with 1 line:

 arr = Rng.Value

I explain this a bit (please read the comments):

Dim rng As Range
 Set rng = Range("A1:A2") 'multiple cells always make arr as 2D array
    arr = rng 'arr is a 2D array

 Dim rng As Range
 Set rng = Range("A1") 'a single cell make arr as variant
    arr = rng 'arr is variant

to create 2D array from a single cell

 Dim rng As Range
 Set rng = Range("A1")
        ReDim arr(1 To 1, 1 To 1)
        arr(1, 1) = rng.Value 'arr is a 2D array

To create 2D array in situation that rng could be a single cell or multiple cell:

Dim rng As Range
 Set rng =  ' from single cell or multiple cell
  
    If rng.Cells.Count = 1 Then  'if rng is a single cell
        ReDim arr(1 To 1, 1 To 1)
        arr(1, 1) = rng.Value 'arr is a 2D array
    Else
        arr = rng.Value 'arr is a 2D array
    End If

 

[wsnyder]

Good point Akuini
That solved it - thanks!
-w

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.