Copy one cell based on values of others

[Chris L]

I have a table of data, Column A is time, Columns C-F are temperature data, each row is a 30 second increment.
I have to copy the time in column A based on the first cell greater or equal to a value.
in the attached sheet I need to find the first cell in range ("C2: F63") greater or equal to "I2" ( The first cell in green fill color) and Copy the time in "A" (16:36:38) to "K1"

 

[AlanSidman]

Deleted. Interpreted needs incorrectly.

 

[Marc L]

A beginner level VBA demonstration for starters :​

Sub Demo1
    Dim T@, Rc as Range
        T = [I2].Value
    For Each Rc In Range("C2", [F1].End(xlDown))
        If Rc.Value >= T Then Cells(Rc.Row, 1).Copy [K1]: Exit For
    Next
        If Rc Is Nothing Then [K1].ClearContents Else Set Rc = Nothing
End Sub

 

[Frank2021]

Hi,

I’m sending you a solution using formulas only.
Another possible solution would be to use Excel 365, but I’ve kept it neutral so it also works for other versions.
If you liked it, don’t forget to give it a thumbs up


Best regards.

 

[Chris L]

A beginner level VBA demonstration for starters :​

Sub Demo1
    Dim T@, Rc as Range
        T = [I2].Value
    For Each Rc In Range("C2", [F1].End(xlDown))
        If Rc.Value >= T Then Cells(Rc.Row, 1).Copy [K1]: Exit For
    Next
        If Rc Is Nothing Then [K1].ClearContents Else Set Rc = Nothing
End Sub

Marc, as always thank you for your help.
This did exactly what I/our customer wanted. Now they have added a more conditions.

I need to find the first temp in columns (C:F) greater or equal to I2 and pressure in column B greater or equal to J2 (16:47:08)

 

[Marc L]

According to the initial attachment, considering column B is already sorted, a more conditions VBA demonstration :​

Sub Demo2
    Dim T@, L&, R&, Rc as Range
        T = [J2].Value
        L = [A1].End(xlDown).Row
    For R = 2 To L
     If Cells(R, 2).Value >= T Then
            T = [I2].Value
        For Each Rc In Range(Cells(R, 3), Cells(L, 6))
            If Rc.Value >= T Then Cells(Rc.Row, 1).Copy [K1]: Set Rc = Nothing: Exit Sub
        Next
            Exit For
     End If
    Next
        [K1].ClearContents
End Sub

​

The same with a VBA array variation :​

Sub Demo2a
        V = [A1].CurrentRegion.Columns("A:F").Value
        W = [I2:J2].Value
    For R = 2 To UBound(V)
     If V(R, 2) >= W(1, 2) Then
        For C = 3 To 6
            If V(R, C) >= W(1, 1) Then [K1] = V(R, 1): Exit Sub
        Next
     End If
    Next
        [K1].ClearContents
End Sub