Variant Array issue when range only has 1 cell?

[Temeraire24]

I just registered here at Mr. Excel, so if I am posting my question incorrectly please advise.

The following section of code works perfectly as long as the range has more than 1 row, however I get a "error 13; Type mismatch" if the range in my array only has 1 row.
How do I correct this so that if the range only has 1 row it does not error?


Dim MyArray() As Variant
Dim ptclassrow As Long
ptclassrow = Sheets("Instructions").Cells(Rows.Count, ptclasscolumn).End(xlUp).Row
MyArray = Sheets("Instructions").Range(Cells(2, ptclasscolumn), Cells(ptclassrow, ptclasscolumn)).Value2

 

[dmt32]

Hi
welcome to forum

Try adding line shown & see if resolves your issue

Dim MyArray() As Variant
Dim ptclassrow As Long
ptclassrow = Sheets("Instructions").Cells(Rows.Count, ptclasscolumn).End(xlUp).Row
MyArray = Sheets("Instructions").Range(Cells(2, ptclasscolumn), Cells(ptclassrow, ptclasscolumn)).Value2
If Not IsArray(MyArray) Then MyArray = Array(MyArray)

Dave

 

[Fluff]

Hi & welcome to MrExcel.
Change the declaration to

Dim MyArray As Variant

 

[Rick Rothstein]

@Dave,

Couple of possible problems with your suggested solution. First, the array that the Array function creates is one-dimensional whereas the array created when a multi-cell range is assigned to a Variant is always two-dimensional. Second, unless Option Base 1 is used, the array created by the Array function will be zero-based whereas an array created by assigning a range to a Variant is always one-based (for each dimension).

 

[Fluff]

I would also recommend using the code like this

   Dim MyArray As Variant
   Dim ptclassrow As Long
   With Sheets("Instructions")
      ptclassrow = .Cells(Rows.Count, ptclasscolumn).End(xlUp).Row
      MyArray = .Range(.Cells(2, ptclasscolumn), .Cells(ptclassrow, ptclasscolumn)).Value2
   End With

Otherwise you will get an error if the Instructions sheet is not active when the code is run.

 

[dmt32]

@Dave,

Couple of possible problems with your suggested solution. First, the array that the Array function creates is one-dimensional whereas the array created when a multi-cell range is assigned to a Variant is always two-dimensional. Second, unless Option Base 1 is used, the array created by the Array function will be zero-based whereas an array created by assigning a range to a Variant is always one-based (for each dimension).

Thanks & Agree but OP declared range having just one Cell & I sort of guessed that may be using array to load a control like combobox which if this is the case, suggestion should resolve the error but until OP responds - just a guess Rick.

Dave

 

[Temeraire24]

Hi & welcome to MrExcel.
Change the declaration to

Dim MyArray As Variant

Thank you!! This simple change worked

Thanks & Agree but OP declared range having just one Cell & I sort of guessed that may be using array to load a control like combobox which if this is the case, suggestion should resolve the error but until OP responds - just a guess Rick.

@dmt32 Simply using @Fluff suggestion worked:
Dim MyArray As Variant instead of Dim MyArray () As Variant

What i was making is not always a single cell, in fact it would be less common for it to turn out to be one cell - the code was working until I happened to run it on a file where the range turned out to be one cell. The code i posted is inside a loop, it is getting that range for the array each time through the loop, because the loop is going to the next column each time (ptcolumn +1), and each column can have a variable amount of rows (ptclassrow is last row of whatever column ptcolumn is each time through the loop).

 

[Fluff]

Glad we could help & thanks for the feedback.