Looping through 2-dimensional array


Board Regular
Nov 11, 2011
Hi there,

Here is my task:

I have a database of ~60,000 objects located at different parts of the world. Each object has 2 parameters to identify it on the surface of the Earth - unique Latitude (column A) and Longitude (column B) coordinates. I need a formula (or, to create a macro) which will loop through this dbase of coordinates and will list only those which are located within a perimeter of a certain square. E.g., say a user wants to see which objects are located between point_A = 30.000 deg (latitude), 40.000 deg (longitude) and point_B = 40.000 deg, 50.000 deg.

I tried VLOOKUP, COUNTIFS, etc. and the following macro endless number of times but they all don't work for me: :(

Public Sub InAndOut()
    Dim i As Integer
    Dim j As Integer
    Dim myArray(99, 1) As Integer
    For i = 1 To 100
        For j = 0 To 99
            myArray(j, 1) = Range("Ai:Bi").Value
            If myArray(j, 1) > Range("Ci:Di").Value Then
            Range("Ei:Fi").Value = myArray(j, 1)
            End If
        Next j
    Next i
End Sub
I will be grateful if you help me to solve this problem with either one of two:
(i) native Excel formula(s) - priority no.1
(ii) VBA macro - priority no.2, if it isn't possible to solve using formulas.

Thank you!

Rustam Alhas
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
It would be easy to do with formulas. Use an additional column with the formula


and copy down.

The cells with a result of 1 are in the box defined by lat1, lat2, lon1, and lon2.
Upvote 0
Hi Rustam
Welcome to the board

You just have to select the 2 columns and use autofilter setting the bounds you want for the values.
Upvote 0
Thank you for your prompt replies! But, unfortunately, this is not what I want to achieve... Let me elaborate the problem.

I have 2 worksheets - 1st contains a dbase of objects as follows:

Rich (BB code):
ObjectID     Latitude    Longitude
AAAA         30.123456   40.123456
BBBB         40.123456   50.123456
CCCC         50.123456   60.123456
DDDD         60.123456   70.123456
...          ...         ...
...          ...         ...
ZZZZ         90.123456   100.123456
The 2nd worksheet is for user input. Let's say he enters "CCCC" in Sheet2!$A$1. Excel will automatically calculate coordinates of two points located at:
(1) 100 nm/315deg NW from CCCC
(2) 100 nm/225deg SE from CCCC

and define a square with top-left (NW) & bottom-right (SE) coordinates and write them down to B1:C1 on the same sheet {NW lat & lon} & B2:C2 {SE lat & lon} (which are hidden from the user to avoid clutter). E.g., NW = 45.123456|50.000000 & SE = 55.123456|60.000000. This part of the problem is easy and solved!

Now, as soon as the area is defined, what I want to achieve is to look up at dbase on the 1st worksheet and automatically list on the 2nd sheet only those objects which are located within the perimeter of defined area, i.e. between NW & SE coordinates!

The problem is I don't have a clue how to make Excel to lookup and compare both "LAT1 | LON1" & "LAT2 | LON2" values at the same time... Since dbase represents a 2-dimensional array - one point having a unique coordinates with two parameters - it's a must to compare any two points simultaneously. E.g., if looking up at Latitudes column without verifying data in Longitude column as well, it's possible that the looked up value is located outside the searched area even though latitudes are the same!

Thank you very much!!!
Upvote 0

If you wanted to do it manually you'd just have to use the autofilter and you'd get your list directly.

Since you want the list to be displayed automatically in the other sheet, use the Advanced Filter with the option Copy, to extract the data that satisfies the criteria.

If you are not used to the Advanced Filter in vba, do it one time manually and record the macro.

To generate the list you either have a button that triggers the execution of the macro, or trigger in the worksheet Change event.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back