find the X Y coordinate using length and distance.

Dear Sir,
The column B and C are showings line of coordinate. we find coordinates around the point based on length (Column F) and distance (Column G) showing in excel.
See attached files to know better

Thank in advance..

Results should be from the formula OR vba function.

Maybe :

Please Login or Register  to view this content.

Result is on Sheet1!Y3:Z34

I thought that the line orientation between two consecutive nodes will always located on quadrant I (just like the data on your first example file), never thought that your data (on the other files which I didn't see before) can be located on all quadrants (I to IV). Here is the amended code :

Please Login or Register  to view this content.

Now for first file (on post #1) and "Test2.xls" file, it will show the same result with your manual calculation.


As for file "Test3.xls" file, there are 3 nodes different with your manual calculation, they are :
- SL 9 (at row 11)
- SL 37 (at row 39)
- SL 93 (at row 95)
All these 3 nodes are located exactly on base nodes at table Sheet1!A:C (not interpolated liked other nodes), for example :
- SL 9 (at row 11) of right table, is exactly at SL 6 (at row 8) of left table
- SL 37 (at row 39) of right table, is exactly at SL 24 (at row 26) of left table
- SL 93 (at row 95) of right table, is exactly at SL 47 (at row 49) of left table
and I don't know your manual formula that yields these 3 nodes (I have searched for hours, and still don't know what formula you are using for them).

Originally Posted by sanju2323

If possible, give the code in the VBA function.

No, it is too complex and will slow down the calculation very much.

Hi Sir,
I got this coordinate from AutoCAD software manually, it was a long process, but due to your code it is going to be a great time saver.
I think there is a small error in the coordinates due to the use of different software. But no matter then it is enough for me, anyway you have helped me a lot. I am very happy with this. Thank you..
Have a good day

I think I found a formula that is used by AutoCAD for those nodes that located exactly at a based node.
They are not using the line slope between previous node and current node [P(i-1) -> P(i)], or line slope between current node and next node [P(i) -> P(i+1)], but rather, they use the line slope between previous node and next node [P(i-1) -> P(i+1)], which is actually a strange approach.

By using this formula, for those nodes that located exactly at a based node, the difference between AutoCAD and mine result becomes drastically smaller.

For example, for file "Test3.xls", for SL 37 (at row 39) :
- using AutoCAD (your manual result) : 53.16 , -35.445
- using previous method (sub Test2) : 50.2908 , -35.5304
- using new method (sub Test3) : 53.2982 , -35.4651
so the improvement is (53.16 - 50.2908) / (53.16 - 53.2982) = 2,076 %

Please Login or Register  to view this content.

Yes, that is what actually I want to tell you.

If the point is located between two base nodes, for example if the point is located on half way between point 23 and 24, than there is no problem at all, this point can use the slope line (angle) of line 23-24.

But if the point is located exactly at the same place with a base nodes, for example if the point is located at point 24, then AutoCAD will give result that is quite strange number. AutoCAD is not using angle of line 23-24, neither using angle of line 24-25 (either one is legal, because point 24 is part of line 23-24, and also part of line 24-25, so technically if we use one of them, it is technically correct). But rather, AutoCAD is seemed to use the slope line (imaginary line) of point 23-25 (skipping point 24, the one we are looking for).

And by using this method, the result is practically now same between AutoCAD result and my sub (as I described in my previous post, the sameness is increased by 2000%).

=====================

After all, I've been pleased now with the result using this method, so no further enhancement will be made.

Thanks for marking the thread as solved.


Regards

Hi Sir, I know that this code has been worked hard to make, It was not an easy task I am also surprised that this is possible. It's just an expert can do. And after looking at points number 9,37,47 in AutoCAD, that coordinate is not a problem.
Thank you for making this code.

Hi Sir,
can this topic be reversed, the length and distance can be detected from the line and point coordinates. I already have an excel file that can solve the issue but it is a difficult one. It can only know one line from coordinates. This is another way to find out from several coordinates of the line.