CODEWITHSUNDEEP
arraysexcelvba
BrettRussellAce
BrettRussellAce

Reputation: 37

Type mismatch error when creating a vba array

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.

Upvotes: 1

Views: 921

Answers (1)

Rory
Rory

Reputation: 34075

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

Upvotes: 3

Related Questions