PDA

View Full Version : Solved: Application.Max problem



av8tordude
02-13-2012, 09:48 PM
Why does my code error if i have less than 2 items in my list? If I use On Error Resume Next, it will not show the list.

Example below works fine

Names
FAA
CAA

but, example below errors

Names
FAA

see attached

mikerickson
02-13-2012, 10:56 PM
The problem originates from the odditiy that the .Value of a multi-cell range is an array (type V() ), but the .Value of a single cell range is a value (type string, double, boolean or error)

The .List property requires an array, single values will cause an error.

Try this to handle the single cell case.
Private Sub UserForm_Activate()
Dim MerLists As Variant

With Range("BI11:BI" & Application.Max(11, Range("BI" & Rows.Count).End(xlUp).Row))
If .Cells.Count Then
MerLists = Array(.Value)
Else
MerLists = .Value
End If
End With

cboName.List = MerLists
End Sub

av8tordude
02-13-2012, 11:04 PM
Thank you Mike :-)

av8tordude
02-13-2012, 11:45 PM
Mike, I notice if I have more than one name in the list, it will not show any names on the list.

Bob Phillips
02-14-2012, 01:48 AM
Private Sub UserForm_Activate()
Dim MerLists As Variant
Dim rng As Range

Set rng = Range("BI11:BI" & Application.Max(11, Range("BI" & Rows.Count).End(xlUp).Row))
With Me.cboName

If rng.Cells.Count > 1 Then

MerLists = Application.Transpose(rng)
.List = MerLists
Else

.AddItem rng.Value
.ListIndex = 0
End If
End With
End Sub

mikerickson
02-14-2012, 07:24 AM
Oops

Private Sub UserForm_Activate()
Dim MerLists As Variant

With Range("BI11:BI" & Application.Max(11, Range("BI" & Rows.Count).End(xlUp).Row))
If .Cells.Count = 1 Then
MerLists = Array(.Value)
Else
MerLists = .Value
End If
End With

cboName.List = MerLists
End Sub