Type mismatch error in VBA in populating an array

Question

I 'm defining an array like this and populating it directly without using any loops

But its throwing error "Type mismatch error"

Dim battarray() As Integer
x = Sheets("Names").Range("a4")
ReDim battarray(x) As Integer
battarray() = Array(40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50)

Can some one help me fix this

Remove As Integer and try again. Array(40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50) creates an array of variant, which cant be assigned to an array of integers. — shahkalpesh
– shahkalpesh, Commented Apr 30, 2014 at 6:04
@bernieIn Sheets("Names").Range("a4") is an integer which gives dynamic size to the array, based on that Array(40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50) also changes — Gajju
– Gajju, Commented Apr 30, 2014 at 6:07
What line is throwing 'Type Mismatch'? Also, did you remove As Integer from line 1 and line 3 in the sample code you posted? — shahkalpesh
– shahkalpesh, Commented Apr 30, 2014 at 6:22

Jean-François Corbett · Accepted Answer · 2014-04-30 06:42:31Z

3

Remove both instances of As Integer, and (optionally) replace them by As Variant.

Array(40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50) creates an array of type Variant. This cannot be assigned to an array of type Integer.

Note that if you're going to hard-code the contents of battarray, there's no point ReDimming the array beforehand. This is sufficient:

Dim battarray() As Variant
battarray() = Array(40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50)

Collectives™ on Stack Overflow

Type mismatch error in VBA in populating an 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