Type Mismatch

[arpd123]

Hi there. I am an occasional VBA programmer and have run into a type mismatch problem that has me stumped. I am using the ancient 2007 edition of Excel (not my choice) and the following code in my macro works:

Set rngCohort = wsScrap.Range("A1:A" & lngScrapLast)
varCohort = rngCohort.Value

(This uses so-called Hungarian notation so that you may see the data type of the variables and objects used).

However, cell A1 contains a header, and I do not want to add that to my array, so I made the following change:

Set rngCohort = wsScrap.Range("A2:A" & lngScrapLast)
varCohort = rngCohort.Value

i.e. I changed the starting cell of the range reference from A1 to A2. Now the VB runtime is telling me that there is a type mismatch error in the varCohort assignment in the second line and the code will no longer run. I do not understand what the difference should be. (varCohort is of type variant, and is to hold a dynamic array).

I did not want to paste the entire code of my macro into this post because it is fairly lengthy. Please tell me if more information is required to answer this question, and thank you for your time.

 

[RoryA]

Welcome to the forum.

Does your variable declaration look like this:

Dim varCohort() As Variant

or like this:

Dim varCohort As Variant

The former would cause an error if lngScrapLast has the value 2, since the code would be trying to return a single value, not an array.

 

[arpd123]

Rory,

Thank you for the welcome and thank you for your response.

Does your variable declaration look like this:

Dim varCohort() As Variant

or like this:

Dim varCohort As Variant

The former would cause an error if lngScrapLast has the value 2, since the code would be trying to return a single value, not an array.

The declaration was, as you deduced, of the former type. I changed it to the latter type and got rid of this type mismatch error, but introduced another further down the code. I would like varCohort to be an array, even when it is assigned a single value (this assigment is made in a loop, so rngCohort can have multiple values, down to a minimum of 1), is there a way to force this behaviour in VBA?

 

[RoryA]

You could check the number of cells and then create an array explicitly if required, like this:

with rngCohort
   If .Count = 1 then
      Redim varCohort(1 to 1, 1 to 1)
      varCohort(1, 1) = .Value
   Else
      varCohort = .Value
   End If
End With

Note that you want to declare it as a 2D array even though it's one value, since that's what you get when you assign a multi-cell range.