[Solved] Find Time with Condition

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:00TRUE
03:00:00TRUE
04:00:00FALSE
05:00:00FALSE
06:00:00TRUE
07:00:00TRUE
08:00:00FALSE
09:00:00FALSE
10:00:00FALSE
11:00:00FALSE
12:00:00TRUE

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,
 
Greetings mFaisial,
Below I have given you a code that could potentially solve your problem.
Code:
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
1685966662344.png
The output will be displayed in the cell D3. The time values that fulfills the condition False are represented here.
 

Attachments

  • Time value based on condition.xlsm
    17.3 KB · Views: 1
Greetings mFaisial,
Below I have given you a code that could potentially solve your problem.
Code:
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,
 

Attachments

  • Time value based on condition.xlsm
    22.4 KB · Views: 1
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.
 

Attachments

  • Copy of Time value based on condition.xlsm
    17.8 KB · Views: 2
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,
 

Attachments

  • Copy of Time value based on condition (2).xlsm
    21.5 KB · Views: 0

Online statistics

Members online
0
Guests online
6
Total visitors
6

Forum statistics

Threads
287
Messages
1,240
Members
508
Latest member
HaroldDyeme
Top