Survey (GPS ) alignment Macro

[n043480]

Hi Gents,

I have a set of date (survey points) containing easting, northing, elevation and comments in that particular order.
What i want is to automatically change the easting northing to align them in a straight line.

391142.8623 6473828.464 67.77032928 S193236
391142.9987 6473829.02 67.54849584
391143.3561 6473831.154 67.52179536 RD
391143.491 6473834.414 67.40502648 RD
391143.6102 6473836.992 67.5060372 RD
391143.4937 6473839.308 67.4464488 RD
391143.5487 6473840.713 67.58327352
391143.8173 6473844.055 67.69114224
391143.7945 6473847.183 67.88060592
391144.1361 6473848.613 67.79623728
391144.2053 6473849.559 67.78322232
391144.1778 6473852.982 67.79770032
391144.1163 6473854.854 67.91078112 S193235
391144.2476 6473855.345 67.9068492
391144.3708 6473858.665 67.54380192
391144.45 6473859.811 67.37570472
391144.656 6473863.948 67.4513256
391144.7692 6473866.155 67.38317232
391145.0588 6473870.506 67.5197532
391145.1938 6473873.151 67.67715192
391145.6578 6473874.55 67.49603976
391145.4067 6473877.498 67.41816336
391145.2175 6473879.265 68.01252336 S192998

lets say that is my data:

the last column containing data that starts with an "S" are the fix points:

what I want is to align the data in between the two fix points back into the straight line formed between the two fix points:

steps:
1. calculate slope of two fix points (m):
2. define equation 1: using y-y1 - m(x-x1) = 0
3. define equation 2 (perpendicular line) --> (new y-y1) - [(-1/m)(new x-x1)] = 0
4. use problem solver to generate the new easting northing for the points in between the two fix points.
5. go to the next set of straight line and repeat steps above until finished.

I have knowledge of C++ and literally no clue on VB unless i use the macro recorder.

Anyone please help?

regards,
Mark

[mike7952]

If you record the Macro, we can improve the recorded Macro

[Crooza]

Hi and welcome

Try this on your Fig 3 file

Please Login or Register  to view this content.

It should do the full translation (as well as put some of the formulas in for your lines). The final translated values will be in columns N and O

You'll need to make sure solver is turned on in VBA though so in your VBA editor once you've pasted this code go to tools / references then tick solver

[Crooza]

Did the above macro fix your problem or is it still not working. I graphed the raw data and the result and it appears the translation to a straight line works graphically. Some feedback on whether this still needs work would be helpful.