Straight in Poker (Hold'em)

Hi,
I don't even know how to word this question without being confusing.. so bare with me.

I have poker (holdem) hand question. Straights in particular.
Most of you if not all probably know what a straight in poker is so, my question is:

If I have a hand with 2 cards that are not paired (AB, but not AA)
and I have a community card of 3 cards that are also not paired, (ABC, but not AAA, AAB, ABB)
How do I determine if those 5 cards make a straight?

I tried to list them in ranking: A, K, Q, J, T, 9, 8, 7, 6, 5, 4, 3, 2
but since A can be after K AND before 2... I don't know...

I've listed out whole straight possible hand + board combination that makes a straight.
The list is pretty long as you can imagine.
I can post that list if you require but I'm sure there's no need for it.

To be honest, I don't even know where to start.. Can you help?

Nice, that's perfect.
However, I require to do this in VBA, is there any way to do this strictly in VBA and, if possible, without typing to cells?

I said above that this was for Holdem, however it is for Omaha Hi.
The reason I said Holdem was because it is more popular and with what I do below, it won't matter.
The rules are slightly different in that you have to use two cards (out of four cards) from your hand with three of the community cards.

I have a macro that checks:
card1 + card2 on board
card1 + card3 on board
card1 + card4 on board
card2 + card3 on board
card2 + card4 on board
card3 + card4 on board

For example, AAKK on board QJT
card1(A) + card2(A) on board(QJT) would be one pair
however, card1(A) + card3(K) on board QJT would be a straight

And at the end of running through this array loop, it determines what was the highest ranking and writes to a single cell.

I realize after looking at RANK() function, that you require a table like what you did with yours so I created that table but I don't know how to do the rest on VBA.

Wow ur a genius. Thank you for all your help. Sorry for the trouble

Hi there,

Hope you're all ok.

Quick question here, I am having trouble to adapt this VBA script so it can find 5 straights cards in a set that is not necessarily 5 cards. Sometimes the set can be composed of 6 or 7 cards.

Similarly, if the set is 5 - 6 - 7 cards, I'd like to be able to find the highest number of straight cards contained in the set (even if not equal to 5).

Do you have any recommendation on how to proceed here?

Cheers