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]

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.

Here is a function which you can call from either a worksheet formula or other VBA code that will take your values in the order you originally gave them and return True if there is an overlap and False otherwise...

Function Fits(A As Long, B As Long, C As Long, D As Long) As Boolean
  Fits = Not Intersect(Rows(A & ":" & B), Rows(C & ":" & D)) Is Nothing
End Function

 

[DaveHappyNorm]

Hi Rick

This has really helped me a great deal so thank you.

I have tried this code and it does work however if I had values such as:-

A = 48
B = 51
C = 20
D = 48

Then your code returns True but in this case it should be false. When D = A then the statement is false, when D> A then the statement should be true.

How can the code be amended to account for this?

 

[Rick Rothstein]

I have tried this code and it does work however if I had values such as:-

A = 48
B = 51
C = 20
D = 48

Then your code returns True but in this case it should be false. When D = A then the statement is false, when D> A then the statement should be true.

Give this replacement function a try...

[table="width: 500"]
[tr]
	[td]Function Fits(A As Long, B As Long, C As Long, D As Long) As Boolean
  If Not Intersect(Rows(A & ":" & B), Rows(C & ":" & D)) Is Nothing Then
    Fits = Intersect(Range("A" & A & ":A" & B), Range("A" & C & ":A" & D)).Count > 1
  End If
End Function[/td]
[/tr]
[/table]

 

[DaveHappyNorm]

Hi Rick

Thanks again, I have used your function to pass in the variables A B C D through a sub procedure, the problem is that when the numbers get large such as 5 or 6 digits then the function does not work, below is the code that I am using:-


"Function Fits(A As Long, B As Long, C As Long, D As Long) As Boolean
If Not Intersect(Rows(A & ":" & B), Rows(C & ":" & D)) Is Nothing Then
Fits = Intersect(Range("A" & A & ":A" & B), Range("A" & C & ":A" & D)).Count > 1
End If
End Function


Sub Rick()

Dim START_MILES As Long
Dim END_MILES As Long
Dim MST_START_MILES As Long
Dim MST_END_MILES As Long

START_MILES = 844080
END_MILES = 89760
MST_START_MILES = 83939
MST_END_MILES = 84186

Cells(1, 193) = Fits(START_MILES, END_MILES, MST_START_MILES, MST_END_MILES)

End Sub"

I have tried to amend the data type to Double but still does not work, do you have any idea?

Thanks

 

[Rick Rothstein]

Hi Rick

Thanks again, I have used your function to pass in the variables A B C D through a sub procedure, the problem is that when the numbers get large such as 5 or 6 digits then the function does not work...

That is why I asked you this in Message #2 ... "can you post an example (or two if there are different patterns) of what your part numbers really look like?" to which you replied in Message #9 with "The numbers shown are real examples and do not contain any decimal numbers". Since the numbers you posted were 2 digits long, I never expected them to ever be 5 or 6 digits long. With that said, the code I posted should work with numbers up to 6 digit long numbers. Describe exactly what you mean when you say that my code "does not work"... in what way does it not work? This probably does not affect your problem, but I do note that your START_MILES value is 6 digits long while all of the other values are only 5 digits long.

 

[DaveHappyNorm]

Hi Rick

The numbers I quoted are real life numbers however the values I pass into your function start low i.e. 1 or 2 digits long but then larger digit values such as 4 or 5 or 6 digits are also passed in.

I failed to realise that this was important as I assumed that if it works with smaller numbers then it should work with larger numbers - my apologies.

The error that I am getting is a Run - Time Error 13, Type Mismatch, the line the error shows up on is:-

If Not Intersect(Rows(A & ":" & B), Rows(C & ":" & D)) Is Nothing Then

Not sure what is going on.

My apologies again for the typo the values for A B C D should be:-

START_MILES = 84480
END_MILES = 89760
MST_START_MILES = 83939
MST_END_MILES = 84186

 

[Rick Rothstein]

The error that I am getting is a Run - Time Error 13, Type Mismatch, the line the error shows up on is:-

I too am using XL2010 and when I run the code you posted in Message #14 (with the START_MILES value corrected as you indicated), it works fine... I do not get the error you are indicating. I am not sure what to tell you as I cannot duplicate your problem here.

 

[DaveHappyNorm]

Hi Rick

If I close excel, create a new workbook and run the code from this new workbook with all other workbook closed it works fine????????????

I must be doing something wrong, is it possible that the values of Start_Miles, End_Miles, MST_Start_Miles and MST_End_Miles are not being cleared from memory?