VBA between function

Question

I was trying to find the best way that I could do the following in VBA:

I have a list of coordinates where an event happened (its a longer list but just to give an example):

Location_Easting

I need to check if any of those coordinates fall into any of the 2 eastings given in a different tab, so essentially checking each of the event eastings (say they are in column B) if any of them falls in between 2 coordinates given in columns (say B and C) on a different tab.

What would be the best way to write this loop?

Look at the `Match` and/or `Index` functions in VBA. What have you tried so far? — David Zemens, Dec 22 '16 at 16:31
http://stackoverflow.com/questions/16947199/find-if-value-exists-on-other-worksheet-excel — David Zemens, Dec 22 '16 at 16:32
http://stackoverflow.com/questions/23061935/check-if-an-excel-cell-exists-on-another-worksheet-in-a-column-and-return-the — David Zemens, Dec 22 '16 at 16:32
http://stackoverflow.com/questions/21714151/vba-check-value-from-column-a-exists-in-another-workbook — David Zemens, Dec 22 '16 at 16:33

bobajob · Answer 1 · 2016-12-22T16:28:57.720

1

You can do this with a simple excel formula:

=COUNTIFS(Sheet2!$B$1:$B$100,"<"&$B1,Sheet2!$C$1:$C$100,">"&$B1)

This, put in column C of the sheet with the event eastings and then copied down, will give the number of coordinate pairs in Sheet2 columns B and C that the event eastings fall in between.

Edit: To get the row number of the coordinate pair that the easting falls in between instead, enter the following as an array formula (using Ctrl+Shift+Enter) in C1, then copy it down the column:

=MATCH(1,(B1>Sheet2!$B$1:$B$100)*(B1<Sheet2!$C$1:$C$100),0)

Edit 2: To get the row number of further coordinate pairs that the easting falls in between, enter the following as an array formula (using Ctrl+Shift+Enter) in D1, then copy it down the column, then copy across as far a necessary to pick up all pairs:

=C1+(MATCH(1,($B$1>OFFSET(Sheet2!$B$1,C1,0,100))*($B$1<OFFSET(Sheet2!$C$1,C1,0,100)),0))

edited Dec 22 '16 at 16:28

answered Dec 22 '16 at 11:40

bobajob

1,192
6
12

thank you , however I also want to return a row number of 2 coordinates where my event number falls into? Does this make sense? I want to know where it happens, which row – Katya Pas Dec 22 '16 at 12:11
That's a different question, but I've added a solution to that one as well. – bobajob Dec 22 '16 at 12:22
thank you but I need to see the row numbers for each time when event easting falls between eastings in sheet 2 – Katya Pas Dec 22 '16 at 16:19
OK, this is possible, as I've demonstrated, but it's getting a bit messy. I'm beginning to think I should have started this in vba after all! – bobajob Dec 22 '16 at 16:32
thank you, yes , do you think there is a way to do this in VBA? Sorry I was on leave and it took me a while to respond – Katya Pas Jan 09 '17 at 09:03

VBA between function

1 Answers1