CODEWITHSUNDEEP
excelvbadynamiclistboxnamed-ranges
Taher
Taher

Reputation: 593

VBA Dynamic Named Range as Listbox RowSource Error

I have a dynamic named range that returns a list (array) of some values that are not assigned but once all values are assigned the list returns #N/A

Formula (Array):

=INDEX(EquipTable[Equip], MATCH(0,EquipTable[Flag],0), 1):INDEX(EquipTable[Equip], MATCH(0,EquipTable[Flag],0)+COUNTIF(EquipTable[Flag],0)-1, 1)

Problem is that I am using this Ranged named as a Listbox.RowSource and I can't find a way to check for the value of the named range or even to check whether its returns errors or not.

I used:

Application.WorksheetFunction.IsError(ActiveWorkbook.Names("UnassignedEquipment"))

But its always returning False

I also used:

IsEmpty(ActiveWorkbook.Names("UnassignedEquipment"))

and

IsError(ActiveWorkbook.Names("UnassignedEquipment"))

Upvotes: 0

Views: 338

Answers (1)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

One way is to use IFERROR in the formula used for the named range so that if the named range doesn't return anything, it refers to a specific range/cell.

=IFERROR(INDEX(EquipTable[Equip], MATCH(0,EquipTable[Flag],0), 1):INDEX(EquipTable[Equip], MATCH(0,EquipTable[Flag],0)+COUNTIF(EquipTable[Flag],0)-1, 1),A1)

Here A1 is the cell which the named range would refer if nothing a valid range is not returned by the formula. Change A1 in the formula as per your requirement.

Upvotes: 1

Related Questions