Reputation: 1159
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
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