arpd123

New Member
Joined
May 14, 2018
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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:

Code:
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:

Code:
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Welcome to the forum.

Does your variable declaration look like this:

Code:
Dim varCohort() As Variant

or like this:

Code:
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.
 
Upvote 0
Rory,

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

Does your variable declaration look like this:

Code:
Dim varCohort() As Variant

or like this:

Code:
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?
 
Upvote 0
You could check the number of cells and then create an array explicitly if required, like this:

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top