Check If a Range Of Numbers Fits Within Another Range Of Numbers

[DaveHappyNorm]

Hi All

I have a problem which I was hoping someone could help me with.

I want to check if any part a number lies within another number.

For example lets say I had the variables:-

A = 48
B = 51

C = 50
D = 53

In this case I want to check if any numbers between C (50) through to D (53) fits within the range of A (48) through to B (51) - in this case the answer would be Yes because 50 and 51 would fit between A and B.

I hope this makes sense.

Can anyone help?

Thank you.

BTW I am using Excel 2010.

Any VBA code would be greatly appreciated.

 

[Rick Rothstein]

There are multiple ways to approach this depending on what your part numbers really look like... can you post an example (or two if there are different patterns) of what your part numbers really look like?

 

[Joe4]

Let's say that your values are in A1, B1, C1, and D1.
Then this should work:

=OR(AND(MIN(A1:B1)>=C1,MIN(A1:B1)<=D1),AND(MAX(A1:B1)>=C1,MAX(A1:B1)<=D1))

 

[DaveHappyNorm]

Hi Joe

Thanks for he response, if there anyway of converting this to VBA because the values of A and B will change as well as C and D.

I have to loop through about 200 rows and A and B are taken from one workbook and C and D is taken from another, so a VBA solution is what I am after.

 

[Rick Rothstein]

Hi Joe

Thanks for he response...

Is there any chance I can get you to answer the question I asked in Message #2 ?

 

[DaveHappyNorm]

Rick Can you see this post?

 

[Joe4]

It shouldn't be too hard to build that same logic into VBA. It is just a series of AND/OR, Greater Than/Less Than operations.

As Rick mentioned, if you need help with that, you are going to need to provide more specific details (general questions can only lead to general solutions).

 

[Rick Rothstein]

Rick Can you see this post?

Which post?

 

[DaveHappyNorm]

Hi Rick

I have tried to reply to you now 3 times and I don't know why I cannot get my reply to post - I am now using the Post Quick Reply button.

The numbers shown are real examples and do not contain any decimal numbers.

I should add that D must be greater than A in order for the statement to be True.

 

[DaveHappyNorm]

Hi Joe4

I have managed to incorporate you formula and it does great which is great.

Is there anyway of amending the formula so that the value D must be greater then A and not equal to it in order for the statement to be true?