VBA Help - If statement equal to a range of cells.

danjames11

New Member
Joined
Apr 22, 2009
Messages
14
I have a range of cells going from B13 all the way to B400. I know how to write this:

If ActiveCell = Range ("B13,B14,B15,B16,B18,and so on") Then....

But I want to be able to do something like B13:B14 instead of writing all the way to 400, But B13:B400 does not work.

Anyone know how to do this.. Once again, I want to see if the active cell is equal to any of the cells in the the range B13 To B400.

Thanks.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
If IsNumeric(Application.Match(ActiveCell.Value, Range("B13:B400"), 0)) Then
 

danjames11

New Member
Joined
Apr 22, 2009
Messages
14
No this didnt work, Thanks Anyway. Any Other Ideas?

It showed the False when I tried this, Thanks
 

sous2817

Well-known Member
Joined
Feb 22, 2008
Messages
2,276
Are you sure you have a match? I tried VoG's suggestion and it worked.

Just as a test, let's say your active cell is D7, have you tried the Match formula in your worksheet to see what it returns?

Code:
=MATCH(D7,B13:B401,0)

If you have a match it will return a number, otherwise you'll get the #N/A error. If I had to guess, I'm thinking you don't have a match eventhough you think you do (leading or trailing spaces perhaps or you're dealing with a number formatted as a number in one cell and a number formatted as text in the other).
 

danjames11

New Member
Joined
Apr 22, 2009
Messages
14
Ahh right, yeah its working now:) Many thanks to you, I'm not sure why I didnt seem to get it to work in the first place but its fine now. You'r very talented and thanks alot!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,566
Messages
5,572,953
Members
412,491
Latest member
tweetytoon
Top