Type mismatch error when creating a vba array

Question

I'm trying to drop a range of cells straight into an array to run some calculations. The range will always be A8:E?, but I never know how many total rows there will be. One of the columns contains string values that I need for determining conditions. I set a variable to find the last row (and no, this data never has any blanks so the method to set it works just fine). This is what I have"

    Sub use_arr()
    Dim rw As Integer

    rw = Worksheets("Sheet1").Range("A8").End(xlDown).Row

    Dim myArr()

    myArr = Worksheets("Sheet1").Range("A8:E" & rw)

    End Sub

But it keeps throwing a "Run_time error '13'" at me.

Arrays are still new to me, someone suggested using them instead of looping through cells for faster execution. So I'm still figuring this out.

If your data is large, I think the type of rw should be long. — Dy.Lee
– Dy.Lee, Commented Nov 6, 2020 at 15:15
Value is required when declaring a dynamic array, and value is not required when it is a static array. That is, if you declare dim myArr, it becomes a static array. — Dy.Lee
– Dy.Lee, Commented Nov 6, 2020 at 15:22

Rory · Accepted Answer · 2020-11-06 15:05:56Z

3

Either declare myArr just as a Variant, not an array, or specify the Value property of the range:

myArr = Worksheets("Sheet1").Range("A8:E" & rw).Value

answered Nov 6, 2020 at 15:05

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Type mismatch error when creating a vba array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related