Range To Array Type Mismatch

wsnyder

Board Regular
Joined
Sep 23, 2018
Messages
131
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:
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
3,549
Office Version
  1. 365
Platform
  1. Windows
I don't fully understand the code but try replacing this:
VBA Code:
    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:
VBA Code:
    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

Board Regular
Joined
Sep 23, 2018
Messages
131
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

Well-known Member
Joined
Feb 1, 2016
Messages
3,549
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:
VBA Code:
    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:
VBA Code:
 arr = Rng.Value

I explain this a bit (please read the comments):
VBA Code:
Dim rng As Range
 Set rng = Range("A1:A2") 'multiple cells always make arr as 2D array
    arr = rng 'arr is a 2D array

VBA Code:
 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
VBA Code:
 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:
VBA Code:
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
 
Solution

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,549
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback.:)
 

Forum statistics

Threads
1,147,482
Messages
5,741,406
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top