locked
Adding filtered column cells RRS feed

  • Question



  • Hi all, I have the following problem:
    On a sheet of an excel workbook filter the "A" column and select only the cells that have the value 5406, I need to do a subtraction of the second cell minus the first cell filtered, the cells need to subtract are located in the "B ", I hope you can help me, greetings
    Wednesday, October 1, 2014 11:57 PM

Answers

  • In Cell C2 you could have entered a formula like 

    =IF(A2=5406,B2-A2,"")

    This would subtract the value of A2 from B2 and stick that in Column C if you have dragged the formula down. 

    If you needed a macro to work on this you could consider something like this 

    Sub Main()
    
        'populate column A with random value
        [A1] = "HeaderA"
        [A2] = 5406
        [A3] = 9999
        [A4] = 12
        [A5] = 5406
        [A6] = 19
        [A7] = 5406
        [A8] = 999
        [A9] = 123
        
        ' constant value for column B
        [B1] = "HeaderB"
        [B2:B9] = 55
        
        SubTract
    
    End Sub
    
    Sub SubTract()
    Application.ScreenUpdating = False
    
        Dim col As Range, cell As Range
        Set col = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        
        col.AutoFilter Field:=1, Criteria1:=5406
        
        For Each cell In col.Offset(1, 0).SpecialCells(xlCellTypeVisible)
            cell.Offset(0, 2) = cell.Offset(0, 1) - cell
        Next
        
        Sheet1.AutoFilterMode = False
    End Sub



    • Marked as answer by aslf010990 Thursday, October 2, 2014 3:22 PM
    Thursday, October 2, 2014 10:44 AM

All replies

  • In Cell C2 you could have entered a formula like 

    =IF(A2=5406,B2-A2,"")

    This would subtract the value of A2 from B2 and stick that in Column C if you have dragged the formula down. 

    If you needed a macro to work on this you could consider something like this 

    Sub Main()
    
        'populate column A with random value
        [A1] = "HeaderA"
        [A2] = 5406
        [A3] = 9999
        [A4] = 12
        [A5] = 5406
        [A6] = 19
        [A7] = 5406
        [A8] = 999
        [A9] = 123
        
        ' constant value for column B
        [B1] = "HeaderB"
        [B2:B9] = 55
        
        SubTract
    
    End Sub
    
    Sub SubTract()
    Application.ScreenUpdating = False
    
        Dim col As Range, cell As Range
        Set col = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        
        col.AutoFilter Field:=1, Criteria1:=5406
        
        For Each cell In col.Offset(1, 0).SpecialCells(xlCellTypeVisible)
            cell.Offset(0, 2) = cell.Offset(0, 1) - cell
        Next
        
        Sheet1.AutoFilterMode = False
    End Sub



    • Marked as answer by aslf010990 Thursday, October 2, 2014 3:22 PM
    Thursday, October 2, 2014 10:44 AM
  • thank you very much is just what I needed, Greetings
    Thursday, October 2, 2014 3:23 PM