Select Case with a Named Range

Hi Tony,


You can only test one cell with a decision code (Select case).

I thought that you only had one cell in your range names.

you will need to use the For Each Loop.

For each c in range("Apple")

select case c.value

case is="Oranges"
Do something.....
case is="Apples"
Do something.....
case is="Pears"
Do something.....
end select

next c

I hope this will do what you want.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Hi Tony,

Yes it is faster if you want the DO SOMETHING..... to be the same for all fruits.

You can run through many ranges if you want:

For each c in range("Apple","Pear","Banana")

select case c.value

case "Oranges","Apples","Lemons"
Do the same for all fruits.........
case else

do something different......
end select

next c




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Hi Tony,

Please find attached workbook.


I have created a macro which should do what you want.

You can put any number of fruits in the range in column a and the macro will test them against the fruit in column C.

I have used an array to store the fruit from column A. I am calling a function in a if decision code.

I hope that this is what you want

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector