Looping through 2-dimensional array

[simurq]

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

 

[shg]

It would be easy to do with formulas. Use an additional column with the formula

=(A2>=lat1)*(A2<=lat2)*(B2>=lon1)*(B2<=lon2)

and copy down.

The cells with a result of 1 are in the box defined by lat1, lat2, lon1, and lon2.

 

[pgc01]

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.

 

[simurq]

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:

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!!!
Rustam

 

[pgc01]

Hi

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.