Select column data in a table based on conditions of another column in same table-CodePudding

I have a 100 row x 3 column table titled "WW60_STRI" where column 1 has increasing distance measurements in feet from 0.00ft at A1 to 999.99ft at A100.

I need to pull data from column 3 based on a range in column 1. specifically, I want to retrieve the max of col 3 range while col 1 ranges are <= 35.87ft. So I only want to look at col 3 range that correlate with col 1 range that is <= variable "s"

What would be the correct VBA syntax to use in my case? Thank you for your help. This is what I have so far:

Sub wshear()

Dim s As Double
s = 35.87

Dim row As Integer

Dim tbl As Range
Dim rng As Range

Set tbl = Range("WW60_STRI")
Set rng = tbl.Columns(1)

'find what position in col 1 the Split is at - position where depth > s
For Each cll In rng
    If cll.Value > s Then
        row = cll.row 'save row number
    Exit For
    End If
    Next
                    
'get length of array
Dim u As Integer
Dim l As Integer
Dim y As Integer
u = UBound(rng)
l = LBound(rng)
y = u - l   1

'use position number "row" to define a range of cells in column 3

'get max/min out of that range
max_above_el_s = Application.WorksheetFunction.Max(Range(tbl(0, 3),tbl(row, 3))) 'I assume format is (row,col) confirm
max_below_el_s = Application.WorksheetFunction.Max(Range(tbl(row 1, 3),tbl(y, 3))) 'I assume format is (row,col) confirm
End Sub

CodePudding user response：

Something like this should work:

Sub wshear()

    Const s As Double = 35.87    'use const for fixed values
    Dim row As Long, arr, mx, v
    
    arr = Range("WW60_STRI").Value 'arr is a 2D array (1 to 100,1 to 3)
    
    For row = 1 To UBound(arr, 1) 'loop over array "rows"
        If arr(row, 1) <= s Then  'within threshold?
            v = arr(row, 3)
            mx = IIf(IsEmpty(mx), v, IIf(v > mx, v, mx)) 'track max value
        End If
    Next row
    
    Debug.Print "Max value is " & mx
    
End Sub