+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    10-10-2013
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    4

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

    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.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,863

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

    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:
    Please Login or Register  to view this content.
    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.
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    10-10-2013
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    4

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

    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.
    Last edited by ArnoJvR; 02-26-2014 at 07:05 AM.

  4. #4
    Registered User
    Join Date
    10-10-2013
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    4

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

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

  5. #5
    Registered User
    Join Date
    10-10-2013
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    4

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

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] find the closest match to given latitude / longitude from data stored in 2 columns
    By florin_excel in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 10-24-2017, 04:54 AM
  2. MGRS to Longitude/Latitude
    By ajocius in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2009, 11:26 AM
  3. Latitude/Longitude Porblem
    By edwardtong694 in forum Excel General
    Replies: 3
    Last Post: 05-20-2009, 12:23 PM
  4. [SOLVED] formula for longitude/latitude
    By Leo in forum Excel General
    Replies: 2
    Last Post: 06-03-2005, 02:05 PM
  5. Help with Latitude and Longitude.
    By LadiFireBug in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-22-2005, 05:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1