Loop though table with dynamic IF Statement using VBA

[dcoker]

Can I loop through a set of data and determine if the current item is within a range of each previous items? In the attached example photo, I have the data. I want to check the following:

If TextBox 24.Top is between TextBox 7.Top and TextBox 7.Bottom OR if TextBox 24.Left is between TextBox 7.Left and TextBox 7.Right. Check if either is true and return true.

Loop to the next TextBox

If TextBox 27.Top is between TextBox. 24.Top and TextBox 24.Bottom OR if TextBox 27.Left is between TextBox 24.Left and TextBox 24.Right
OR TextBox 27.Top is between TextBox.7.Top and TextBox7.Bottom OR if TextBox27.Left is between TextBox7.Left and TextBox7.Right. Check if any is true and return true.

Loop to the next TextBox


I am trying to get the IF/OR statement to grow so that each range of values from all the previous text boxes are checked against the current text box. Is this possible? I have this data stored in a collection as well using VBA. I am just not sure how to proceed. Thanks for reading!

 

[DanteAmor]

It can be done with a formula. Try:

Dante Amor
	I	J	K	L	M	N
1	Name	Top	Left	Bottom	Right	Result
2	TextBox 7	235	240	245	267
3	TextBox 24	245	219	254	242	TRUE
4	TextBox 27	291	283	300	315	FALSE
5	TextBox 39	351	297	360	329	TRUE
6	TextBox 43	269	288	279	320	TRUE
Hoja2

Cell Formulas
Range	Formula
N3:N6	N3	=IF(COUNTIFS($J$2:J2,"<="&J3,$L$2:L2,">="&J3)+COUNTIFS($K$2:K2,"<="&K3,$M$2:M2,">="&K3)>0,TRUE)

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
---------

 

[dcoker]

It can be done with a formula. Try:

Dante Amor
	I	J	K	L	M	N
1	Name	Top	Left	Bottom	Right	Result
2	TextBox 7	235	240	245	267
3	TextBox 24	245	219	254	242	TRUE
4	TextBox 27	291	283	300	315	FALSE
5	TextBox 39	351	297	360	329	TRUE
6	TextBox 43	269	288	279	320	TRUE
Hoja2

Cell Formulas
Range	Formula
N3:N6	N3	=IF(COUNTIFS($J$2:J2,"<="&J3,$L$2:L2,">="&J3)+COUNTIFS($K$2:K2,"<="&K3,$M$2:M2,">="&K3)>0,TRUE)

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
---------

Thank you. I think I can add this in the worksheet and use VBA to check that column. Thanks!

 

[dcoker]

Thank you. I think I can add this in the worksheet and use VBA to check that column. Thanks!

It can be done with a formula. Try:

Dante Amor
	I	J	K	L	M	N
1	Name	Top	Left	Bottom	Right	Result
2	TextBox 7	235	240	245	267
3	TextBox 24	245	219	254	242	TRUE
4	TextBox 27	291	283	300	315	FALSE
5	TextBox 39	351	297	360	329	TRUE
6	TextBox 43	269	288	279	320	TRUE
Hoja2

Cell Formulas
Range	Formula
N3:N6	N3	=IF(COUNTIFS($J$2:J2,"<="&J3,$L$2:L2,">="&J3)+COUNTIFS($K$2:K2,"<="&K3,$M$2:M2,">="&K3)>0,TRUE)

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
---------

Thank you, DanteAmor. The XL2BB Add-In is giving me problems when trying to create a mini-sheet of the data and freezes up excel forcing me to End Task in the Task Manager. I believe the formula is not quite right, since the formula increases a range of criteria, so it it seems to eventually returns true for all subsequent text boxes. I added a link to the workbook below.

One Drive Link to workbook