Worksheet ranges> vba arrays, what mistake am I making?

[andydtaylor]

Hi guys,

What am I doing wrong here? Runtime error 13 type mismatch. I get that the error says I am declaring my variables wrong, but I still can't see it.

Thanks,

Sub Permulations()

Dim alloc_series() As Variant
Dim comparison() As Variant
Dim measure() As Variant
With ThisWorkbook.Worksheets("Inputs")
    alloc_series = .Range("Alloc_series")
    comparison = .Range("Comparison")
    measure = .Range("measure")
End With



End Sub

 

[Dendro]

I'm not sure but could you try with making a variable of type range and assigning the named range that variable using 'Set'. After that assign the variable to the array.
This is what i mean:

Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
With ThisWorkbook.Worksheets("Inputs")
Set rng1 = .Range("Alloc_series")
Set rng2 = .Range("Comparison")
Set rng3 = .Range("measure")
alloc_series = rng1
comparison = rng2
measure = rng3
End With

 

[JackDanIce]

Try:

Sub Permulations_v1()

Dim alloc_series()  as Variant
Dim comparison()  as Variant
Dim measure()      as Variant

With ThisWorkbook.Sheets("Inputs")
    alloc_series = .Range("Alloc_series").Value
    comparison = .Range("Comparison").Value
    measure = .Range("measure").Value
End With

End Sub

 

[Rick Rothstein]

Hi guys,

What am I doing wrong here? Runtime error 13 type mismatch. I get that the error says I am declaring my variables wrong, but I still can't see it.

Thanks,

Sub Permulations()

Dim alloc_series() As Variant
Dim comparison() As Variant
Dim measure() As Variant
With ThisWorkbook.Worksheets("Inputs")
    alloc_series = .Range("Alloc_series")
    comparison = .Range("Comparison")
    measure = .Range("measure")
End With

End Sub

Remove the parentheses highlighted in red above... you do not want your variables to be an array of variants (each element of which would contain something), you want them to be variants that end up being assigned a single array each.