CODEWITHSUNDEEP
arraysvbaexcel
Alexander Starbuck
Alexander Starbuck

Reputation: 1159

I get an array "Type mismatch" in Excel and VBA

I get "Type mismatch" when trying to copy a simple one column into an array and back into another column. Here's my code (just the relevant bit):

Sub CopyRangesViaArrays()

 'Declaring variables
 Dim srcWkb As Workbook
 Dim destWkb As Workbook

 Dim srcYears() As Double
 Dim destYears As Range

 Dim L As Long

 Set srcWkb = Workbooks("Arrivals.xlsx")
 Set destWkb = Workbooks("OvernightStays.xlsm")

 L = srcWkb.Worksheets.Count
 z = 0

 'Looping through src Workbook sheets
 For C = 1 To L
    ReDim srcYears(26, 0)
    srcYears = srcWkb.Worksheets(C).Range("A3:A28")

    Set destYears = destWkb.Worksheets(10).Range("A2").Offset(z, 0)
    destYears.Value = srcYears
    z = z + 26
 Next C

As you probably see, I'm also having trouble with offsetting the destination range by the number of years taken into an array (there are 43 Sheets so there will be 43 different srcRanges taken up into an array and written to the destRange).

Many thanks!

EDIT: I've also tried this as per instructions

Dim srcYears As Range
Dim destYears As Range

Breaks at this line:

destYears.Resize(26, 1).Value = srcYears.Value

I tried omitting the last .Value from the problematic line - still nothing.

Upvotes: 1

Views: 554

Answers (1)

David Zemens
David Zemens

Reputation: 53663

If you do Dim srcYears() As Variant, then your code will work. I am not certain about why you can't declare it as a strongly typed array (e.g., As Double) but I'm guessing that a range's .Value, which can be represented as an array, cannot be handled this way, because the .Value array is not necessarily of that data type, and will not be coerced to that type by an assignment. My guess is that a Range.Value is always of type Variant, because a range can contain string, numeric, or error values.

You can assign directly from one range to another:

 Dim srcYears as Range

 For C = 1 To L
    '## not needed: ReDim srcYears(26, 0)
    Set srcYears = srcWkb.Worksheets(C).Range("A3:A28")

    Set destYears = destWkb.Worksheets(10).Range("A2").Offset(z, 0)
    '## resize the destination range to ensure it _
        accommodates the source Range, then assign directly.
    destYears.Resize(26,1).Value = srcYears.Value
    z = z + 26
 Next C

Upvotes: 4

Related Questions