CODEWITHSUNDEEP
excelexcel-formula
user4599
user4599

Reputation: 127

create column based on other columns

earlier I had asked question in wrong way. i have erased it completely and asking again.

enter image description here

suppose there are columns A ,B ,C

c = A > B

after some time, suppose value in A2 changes to 20 now C2 will be true

again after some time, suppose value in A2 changes to 1 now C2 will be false

I want to add another column D such that, whenever value in cell in column C becomes true ,for the first time, corresponding cell in D i.e D2 should be true, and it should remain true even if value in cell c2 in column C keep on changing from true to false and vice versa

same for other rows

I hope I have asked this time clearly.

edit 2: actual problem

values in this column keep on changing throughout the day, lets say values in row number 5 and 6 was true at some point during the day

when i see this data, at eod (end of the day) value in this rows is false

i want to know, for which rows the value in column cr3 was true for at least once in a day

to solve this issue, i wanted to create another column which will contain value as true for this rows, as at some point during the day they had true value in them

enter image description here

Upvotes: 0

Views: 634

Answers (1)

Evil Blue Monkey
Evil Blue Monkey

Reputation: 2609

Try this code in you sheet module:

Private Sub Worksheet_Calculate()
    
    'Declarations.
    Dim RngSource As Range
    Dim RngResult As Range
    Dim DblRow As Double
    
    'Setting RngSource as the first data cell of the third column.
    Set RngSource = Me.Range("C1")
    
    'Resetting RngSource as the whole continuous data of the third column .
    Set RngSource = Me.Range(RngSource, RngSource.End(xlDown))
    
    'Checking if the RngSource is full.
    If Excel.WorksheetFunction.CountBlank(RngSource) = 0 Then
        
        'Setting RngResult as the range offset of RngSource.
        Set RngResult = RngSource.Offset(0, 1)
        
        For DblRow = 1 To RngSource.Rows.Count
            RngResult.Cells(DblRow, 1).Value = ((RngResult.Cells(DblRow, 1).Value Or RngSource.Cells(DblRow, 1).Value) = True)
        Next
        
    End If
    
End Sub

Upvotes: 1

Related Questions