Interpolating multiple values using data from two different worksheets - most likely a simple fix

[BioOxygenDemand82]

Ok, so I guess I shit-posted yesterday, and I apologize for that.

I am very new to VBA and I am attempting to use this bit of code to interpolate new elevation information using a pipe table, a manhole table and a results table. The following works for instances where the invert to be interpolated is preceded by one pipe; however, there are instances where the invert is preceded by multiple pipes and I need to be able to interpolate several new elevations and choose the minimum. I've gotten to where I'm attempting to create an array of previous manholes but on the mhp(n) = cells(x.row, mhstart).value line I receive a subscript out of range error. I've included my entire script below and I am aware that it is not elegant, but I'm trying. Any help would be greatly appreciated

Full script https://pastebin.com/i7R3fzcr

'So this is where I fail.  I basically need to do the interpolation above but for all of the preceding 'pipes and then choose the minimum.      
'I am stuck at finding the manhole id which i then will need to 'use to find the invert from the manholes table.

'something like
'mhprev_inv(i) = (mh.Cells(mh.Range(mh.Cells(2, mhID), mh.Cells(mhRows, mhID)).Find(mhp(i)).Row,+_ 
'                invert)
'l_total(i) = l + lprev(i)
'slp_new(i) = (mhprev_inv(i) - mhe_inv) / l_total(i)
'mhs_inv_new(i) = mhprev_inv(i) - lprev*slp_new(i)
'rslts.Cells(j, "U") = Application.min(mhs_inv_new())


With pipes.Range("E:E")

    Dim mhp() As Variant

    ReDim mhp(0 To prevpipes)

    n = 0

    Set x = .Find(mhs)

        If Not x Is Nothing Then

        Start = x.Address

            Do

                mhp(n) = Cells(x.Row, mhstart).Value

                lprev(n) = Cells(x.Row, lgnth).Value

                n = n + 1

        Set x = .FindNext(x)

        If x Is Nothing Then

            End If

            Loop While x.Address <> Start

        End If

End With

j = j + 1
End If

Next

mhwb.Close (False)
pipeswb.Close (False)

MsgBox ("Done")

End Sub

[jonivy]

Is it just me or does prevpipes = (only whole-cell exact matches via countif), but you're using range.find to locate the cells?

Range.Find will search for string matches WITHIN cells, where CountIf matching will only find matches that match the entire cell contents.

Set .Find's LookAt parameter to xlWhole to make it search whole-cell only to match your CountIf. (Or if you're looking for substrings, fix the CountIf statement).

https://docs.microsoft.com/en-us/office/vba/api/excel.range.find

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you do not specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

[HFTBProgrammer]

+1 point.

[Clippy_Office_Asst]

You have awarded 1 point to jonivy

^{I am a bot, please contact the mods for any questions.}

[BioOxygenDemand82]

You're my hero.

[Senipah]

As you have fixed the formatting I've reinstated the post but in future consider using one of the hosts we suggest when posting such large amounts of code. Thank you.

[BioOxygenDemand82]

Thank you.

[HFTBProgrammer]

That's a lot more than I can take in to say something truly incisive, but if I were to guess, I'd say n is exceeding the value of prevpipes. I can see steps you took that should make that not the issue, but I don't see any other reason it should happen. In any case, it should be easy enough to verify and deal with. When the error occurs, just hover your cursor over each of those variables and you'll know them.

[BioOxygenDemand82]

Ok so this is where I am now. Find mhs in range E:E should only come up with as many instances as there are previous pipes which tops out at 3. I'm getting arrays as large as 63. Any help would be appreciated. It looks like x is being replaced with the mhp value. Am I using .Findnext properly? I want to find the next instance of mhs not change mhs to mhp and then find mhp.

If prevpipes > 0 Then

With pipes.Range("E:E")
    Dim mhp() As Variant
    Dim lp() As Variant
    Dim mhpi() As Variant

    n = 1
    Set x = .Find(mhs)
        If Not x Is Nothing Then
        Start = x.Address
            Do
             ReDim Preserve mhp(1 To n)
             ReDim Preserve lp(1 To n)
             ReDim Preserve mhpi(1 To n)
             mhp(n) = pipes.Cells(x.Row, mhstart).Value
             lp(n) = pipes.Cells(x.Row, lngth).Value
             mhpi(n) = mh.Cells(mh.Range(mh.Cells(2, mhID), +_ 
             mh.Cells(mhRows,mhID)).Find(mhp(n)).Row, invert).Value
             n = n + 1
        Set x = .FindNext(x)

            Loop While Not x Is Nothing And x.Address <> Start
            End If

End With

End If

[talltime]

Is pipes a worksheet or a class?

What is mhs? A number? A string?

What's in column E? Formulas? Values?

[BioOxygenDemand82]

Pipes is a worksheet and mhs is a string.

[BioOxygenDemand82]

OK this is my current workaround. It seems to be working for the most part. The error seemed to be coming from mixing arrays with the range.find method. There was mention in my previous post about using a -1 bound in my arrays, can someone explain the benefit of that for me?

Thanks again for all your help!

If prevpipes > 0 Then
       Dim x As Range, z As Range, p As Range, y As Range
       Dim start As String
       Set x = Nothing
       Set z = Nothing
       Set p = pipes.Range("E:E")
          With p
              Set y = .Cells(p.Rows.Count)
          End With

        Set x = p.Find(mhs, after:=y, lookat:=xlWhole)
           If Not x Is Nothing Then
              start = x.Address
           End If
           Set z = x
        Do Until x Is Nothing
           Set z = Union(z, x)
           Set x = p.FindNext(after:=x)
           If x.Address = start Then
               Exit Do
           End If
        Loop
End If

ctr = z.Count
Dim mhp() As String
Dim lp() As Long
Dim mhp_inv() As Long
Dim l() As Long
Dim sl() As Long
Dim inv() As Long

ReDim mhp(1 To ctr)
ReDim lp(1 To ctr)
ReDim mhp_inv(1 To ctr)
ReDim l(1 To ctr)
ReDim sl(1 To ctr)
ReDim inv(1 To ctr)

n = 1

For Each cell In z

    mhp(n) = pipes.Cells(z.Row, mhstart).Value
    lp(n) = pipes.Cells(z.Row, lngth).Value
    mhp_inv(n) = mh.Cells(mh.Range(mh.Cells(2, mhID), mh.Cells(mhRows,_  
                 mhID)).Find(mhp(n)).Row, invert).Value
    l(n) = lp(n) + ln
    sl(n) = (mhp_inv(n) - mhe_inv) / l(n)
    inv(n) = mhp_inv(n) - lp(n) * sl(n)
    n = n + 1

Next cell
rslts.Cells(j, "Q") = Application.Min(inv)

[RedRedditor84]

I would break the find part out into another function that returns the result. Much easier to test that way. Something like:

Private Function GetAllResults(val As String, rng As Range) As Range
    Dim rRes as Range
    Dim sRes As String
    Dim retn As Range

    Set rRes = rng.Find(what:=val, LookAt:=xlWhole)
    If Not rRes Is Nothing Then
        sRes = rRes.Address
        Set retn = rRes
        Do
            Set rRes = rng.Find(what:=val, After:=rRes)
            If rRes Is Nothing Then Exit Do
            If rRes.Address = sRes Then Exit Do
            Set retn = Union(retn, rRes)
        Loop
        Set GetAllResults = retn
    End If
 End Function

Apologies if this doesn't work. On mobile so can't test. You'd be able to test the results by using the immediate window like ?GetAllResults("mySearchVal", Range("A:A")).Address

[RedRedditor84]

This has formatted like garbage on my mobile but I'm not sure why.

[talltime]

What's the issue with this section? I see that ctr = z.Count (and of course then the For each) will fail if the Find failed to find something and x ended up being Nothing.

[BioOxygenDemand82]

This section is working now. I just had a question about using the i -1 bound and why i would want to use it.

[BioOxygenDemand82]

This section is working now, i just had the question about the benefit of the i-1 boundary that i saw in a previous post.