Find Latitude and Longitude within a radius and within a given slope and return a number

**ArnoJvR** · 02-25-2014, 11:08 AM

I have more than 1000000 coordinates with heights to sort through. The aim is to be able to give a specific radius and check all coordinates within this radius if the slope is more than a maximum slope. If this is the case it need to put the value (in this case) 100 in a new column. The reason for this is we have a reasonable flat terrain but the entire area is filled with Anthills. I need to sort the data. Normal ground points (No Anthills) should be labelled/coded as 200 and anthills as 100. This will allow my program to know the difference between the ground and anthills. In the tab "Input Sheet" I have a small portion of co-ordinates starting from row 8 to row 53 (this will have to extend all the way down to the last row in excel). I need to copy each row starting with row 8 (C8:E8) and paste it in row 2 (C2:E2). Column H indicates if the points are forming an anthill and the code needs to change. I have my final answer in the tab "Final Answer" that I require for my program. Is there any way I can write a VBA code that will check all the point instead of doing it manually.

**Kaper** · 02-25-2014, 02:38 PM

1) comment: calculations on full set of data for each point would take ages. Better approach could be dividing your data on subsets say 30 by 30 meters with 5 meters overlap and calculate the slopes for inner 20 by 20 meters part.
2) as a starting point for prepared subset (as you have shown in sample file) and following suggested algorithm could be:

Sub test()
Dim i As Long
Application.Calculation = xlCalculationManual
For i = 8 To Cells(8, "A").End(xlDown).Row
  Range("C2:E2").Value = Cells(i, "C").Resize(1, 3).Value
  ActiveSheet.Calculate
  Sheets("Final Answer").Cells(i - 6, "D") = IIf(Range("I2") > Range("J2"), 100, 200)
Next i
Application.Calculation = xlCalculationAutomatic
End Sub

Make sure (I think it is not that way now) that formulas calculating distance take into account current row2 data, not point 0,0, nor row 8 data.

**ArnoJvR** · 02-26-2014, 05:00 AM

Thank you Kaper. I will have to agree with your comment of dividing the coordinates in groups and not to try and calculate all the points at once. I made some adjustment in the formula in G2:I2. If you look at the VBA Code and you put your courser after "Next i" and press Ctrl+F8 you will see how your formula does exactly what I need, step for step. It places the 100 code at each point as required. The problem comes on the "Final Answer" sheet. You will see in column "E" I have manually copied all the 100 codes but when you run the VBA code it does not give the answers it calculated for each point from sheet "Input Sheet" column "H" to column "D" in "Final Answer" Sheet. Could you please assist. Column "D" should display the answers as per column "E" on "Final Answer" sheet.

**ArnoJvR** · 02-26-2014, 07:08 AM

I forgot to add the new revised file. Here is the new file.

**ArnoJvR** · 02-27-2014, 04:56 AM

Just an update from my side. I have changed the work sheet to work on one sheet only to reduce any time excel needs to calculate the data. I had a major problem with getting the result with paste special and skip blanks. When you have a if statement and the result of the if statement ends with "" eg. =if(H8=100,100,"") and you copy, paste special "Values" it does not recognise the cell as a blank sell. This can be tested by =isblank(I8) where I8 is the formula =if(H8=100,100,"") and you copy and paste special "Value". I found a way around this by after paste special you use the Find & Replace command. You firs find (Nothing) and in the replace you type some special characters eg: ?@? You then repeat the command by finding your special characters eg: ?@? and in the replace you put (nothing). Please make sure not to use "" to indicate nothing. Delete everything to indicate (Nothing). You can now check the cell with the isblank formula and you will get "True" indicating it is a blank cell. You can only use paste special Skip Blanks now. I am sure there must be a shorter way to accomplish this and I would appreciate any feedback on how to shorten these steps. I will include my worksheet so you can see the macro and hopefully help to shorten the macro. I would like to thank Kaper as he helped me a lot with his input.

Find Latitude and Longitude within a radius and within a given slope and return a number

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Find Latitude and Longitude within a radius and within a given slope and return a number

Re: Find Latitude and Longitude within a radius and within a given slope and return a numb

Re: Find Latitude and Longitude within a radius and within a given slope and return a numb

Re: Find Latitude and Longitude within a radius and within a given slope and return a numb

Re: Find Latitude and Longitude within a radius and within a given slope and return a numb

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] find the closest match to given latitude / longitude from data stored in 2 columns

MGRS to Longitude/Latitude

Latitude/Longitude Porblem

[SOLVED] formula for longitude/latitude

Help with Latitude and Longitude.

Bookmarks

Bookmarks

Posting Permissions