[Solved] Find Time with Condition

[mfaisal.ce]

Dear,

I have a file with 02 columns,
1. Time
2. Status

I need to get the time when the Status is "FALSE".

Example,

02:00:00	TRUE
03:00:00	TRUE
04:00:00	FALSE
05:00:00	FALSE
06:00:00	TRUE
07:00:00	TRUE
08:00:00	FALSE
09:00:00	FALSE
10:00:00	FALSE
11:00:00	FALSE
12:00:00	TRUE

From 4 to 5 and 8 to 11, the value in the Status column is "FALSE"
How to pragmatically extract this information and save in a Single Cell.
Consider Range is not fixed and is changing.

Regards,

 

[Rubayed Razib]

Greetings mFaisial,
Below I have given you a code that could potentially solve your problem.

Sub CheckConditionAndSaveTimeValues()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim timeRange As Range
    Dim lastRow As Long
    Dim condition As Boolean
    Dim timeValues() As String
    Dim timeCount As Integer
    Dim output As String
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    Set dataRange = ws.Range("C3:C" & lastRow)
    Set timeRange = ws.Range("B3:B" & lastRow)
    condition = False
    ReDim timeValues(1 To 1)
    timeCount = 0
    For i = 1 To lastRow
        If dataRange.Cells(i, 1).Value = condition Then
            timeCount = timeCount + 1
            ReDim Preserve timeValues(1 To timeCount)
            timeValues(timeCount) = timeRange.Cells(i, 1).Text
        End If
    Next i
    output = Join(timeValues, ", ")
    ws.Range("D3").Value = output
End Sub

The output will be displayed in the cell D3. The time values that fulfills the condition False are represented here.

 

[mfaisal.ce]

Greetings mFaisial,
Below I have given you a code that could potentially solve your problem.

Sub CheckConditionAndSaveTimeValues()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim timeRange As Range
    Dim lastRow As Long
    Dim condition As Boolean
    Dim timeValues() As String
    Dim timeCount As Integer
    Dim output As String
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    Set dataRange = ws.Range("C3:C" & lastRow)
    Set timeRange = ws.Range("B3:B" & lastRow)
    condition = False
    ReDim timeValues(1 To 1)
    timeCount = 0
    For i = 1 To lastRow
        If dataRange.Cells(i, 1).Value = condition Then
            timeCount = timeCount + 1
            ReDim Preserve timeValues(1 To timeCount)
            timeValues(timeCount) = timeRange.Cells(i, 1).Text
        End If
    Next i
    output = Join(timeValues, ", ")
    ws.Range("D3").Value = output
End Sub

View attachment 364
The output will be displayed in the cell D3. The time values that fulfills the condition False are represented here.

Dear,

Thanks for your help. But i need output slightly in different manner. I have updated the file you shared with my required format. Hopefully, you will sort out.

Regards,

 

[Rubayed Razib]

Greetings mFaisal,
Here is the updated code with the file attached below. Don't forget to change the sheet name and the dataset starting row index and the column index according to your dataset. I hope this will resolve the issue.

 

[mfaisal.ce]

Thanks bro, It worked perfect.

Regards,

 

[mfaisal.ce]

Thanks bro, It worked perfect.

Regards,

Dear,

Sorry to bother you; I need certain more requirements;

1. In earlier file, there was only 1 column with values but now i need to get the time for 02 columns (B,C). B will be True, C will be False and vice versa.
2. Time range is a fix values, how to fix permanent range inside script without getting from the sheet .

Scenario will be,

Script will take values from Column B & C which are TRUE and give the time


Regards,