VBA Help

[cmnewf]

Hello,

Can someone help me to create a module (or other) in VBA for excel for the following pseydo-code:

for each [point] in [pointList]:
do until stop:
for each [point+1] in [pointList]:
if [end]: stop if [point].[measure] >= [point+1].[measure]:
[measure]+= [point+1].[measure] – [point].[measure]
else:
{function}FindNextHighestPoint
{function}InterpolateMeasure
[measure] += [interpolatedMeasure]
if {function}InterpolateMeasure or [end]: stop.

The main point of the code is to have one column with distance, and another column with elevations, and to find the distance from one point to the next highest point. Then to run through all of the locations.

Thanks for your help!

[Artik]

Please attach a workbook with data and show what you want to achieve in it.


For the future. The title of the thread should reflect the problem you are facing, not some general statement about help, because by publishing the thread we already know that you need such help. A more accurate title would be, e.g. "Searching for distance between points" or something similar.

Artik

[cmnewf]

Please attach a workbook with data and show what you want to achieve in it.


For the future. The title of the thread should reflect the problem you are facing, not some general statement about help, because by publishing the thread we already know that you need such help. A more accurate title would be, e.g. "Searching for distance between points" or something similar.

Artik

Thanks for letting me know!

I've attached a file, the problem example is a waterline under a city. If the waterline breaks at any point along the line, what would be the potential length of pipe above that point on the entire segment. It would have the potential within the valley, and they anything outside the valley that would be higher than the peaks. Right now i'm just trying to have a column that can calculate the length, and can apply the length to a volume afterwards.

[p45cal]

If there was a leak here, would the length be zero?
2019-09-08_215350.png

[cmnewf]

If there was a leak here, would the length be zero?
2019-09-08_215350.png

There would be length as there is a higher elevation on the left side of the valley.

[p45cal]

So if there were a leak here instead, just a little more to the right:
2019-09-09_101910.png
then still, that same higher elevation on the left side should be counted.
Unless there is some nuance that I have missed, I think you're doing it right with your formula in column C.

[cmnewf]

So if there were a leak here instead, just a little more to the right:
2019-09-09_101910.png
then still, that same higher elevation on the left side should be counted.
Unless there is some nuance that I have missed, I think you're doing it right with your formula in column C.

That's where the spreadsheet has an error, it shouldn't include anything to the right or left if there's a value higher in between as gravity wouldn't feed it.

[p45cal]

if gravity feeds my diagram in msg#4 it has to feed it too in a point directly below that, as in diagram in msg#6!
I don't think your spreadsheet has an error.

[p45cal]

I've changed my mind on your spreadsheet being correct.
On the other hand it's not as simple as the length between the local peaks on either side of the leak above the elevation of the leak, but I may have misunderstood.
The attached has a button which will add minima and maxima indications to column D, as a first step.
I don't understand your pseudo-code in msg#1, but is it something along the lines of these diagrams below?:
2019-09-09_231314.jpg
and:
2019-09-09_232208.jpg

[p45cal]

Oops, I got that second diagram wrong at the right hand side:
2019-09-10_095930.jpg

edit post posting: I see the answer to "is it something along the lines of these diagrams below?" is 'yes':
2019-09-10_104035.jpg

[cmnewf]

You have understood that exactly correct, any chance you know how to code that?

Oops, I got that second diagram wrong at the right hand side:
2019-09-10_095930.jpg

edit post posting: I see the answer to "is it something along the lines of these diagrams below?" is 'yes':
2019-09-10_104035.jpg

[p45cal]

I haven't worked it out yet - it may be some time.
I gave you a start.

[cmnewf]

Like the maximum and minimums, still trying to wrap my head around that!

I haven't worked it out yet - it may be some time.
I gave you a start.

[cmnewf]

Stared at this for some time and don't understand (sorry, quite new to this!):

If Slope <> PreviousSlope And PreviousSlope <> 0 Then
'Range(Range("B2:B102").Cells(i - 1), Range("B2:B102").Cells(IIf(PreviousSlope = 1, lastup, lastDown))).Offset(, 3).Value = IIf(PreviousSlope = 1, "Max", "Min")

I haven't worked it out yet - it may be some time.
I gave you a start.

[p45cal]

Stared at this for some time and don't understand

That line is quite complex,but note that it starts with an apostrophe which means it's a commet and doesn't get executed. You could enable it and it will place the same results but in column E. If you step through the code one line at a time with F8 on the keyboard, when you get to that line you'll see it update a few cells in column E. I only used it as a visual while debugging.

[Artik]

cmnewf, please do not quote the entire replies predecessor's. If necessary, insert only the relevant passage to which you refer. You needlessly clog the server with quotes.

Artik

[cmnewf]

Thanks Artik and p45cal! Thanks for the explanation, see how that works now!

[Artik]

cmnewf, oh, no merit mine here.

Artik

[p45cal]

Those distances in column A, are they measuring horizontal distance rather than actual pipe length?
It might make quite a difference to spill volume.
To illustrate I'll exaggerate the point: say a portion of the pipe is downhill, angled at 45 degrees. Over a horizontal distance of 1 metre (units are irrelevant) the pipe would have to be 1.41 metres long, some 40% longer (and contain 40% more liquid).

[cmnewf]

Those distances in column A, are they measuring horizontal distance rather than actual pipe length?
It might make quite a difference to spill volume.
To illustrate I'll exaggerate the point: say a portion of the pipe is downhill, angled at 45 degrees. Over a horizontal distance of 1 metre (units are irrelevant) the pipe would have to be 1.41 metres long, some 40% longer (and contain 40% more liquid).

Good question, the distance in column A is 2D (horizontal distance). Over a long enough distance the additional 3D length would be greatly reduced vs looking at one sloped area in particular.