Search code examples
vbaexcelexcel-2013

Filter data in a column and then count


I have recorded a macro to make changes to a sheet. Basically it makes a few changes such as add a column move two columns over and so forth. The thing that I am confused with is adding a small code to give me a count of the total DL and IDL in the MO REAL column L separately and putting the total count on another sheet in the same workbook "Resultados" in cells B17 and C17... Any ideas on how this can be accomplished? Here is the recorded code:

Option Explicit
Sub DefineDL_IDL()

Dim wbTHMacro As Workbook, wsRegulares As Worksheet, wsRegularesDemitidos As Worksheet, wsTempActivos As Worksheet, _
wsTempJA As Worksheet, wsTempFit As Worksheet, wsTempDemitidos As Worksheet, wsPS As Worksheet, wsResultados As Worksheet, _
wsDLList As Worksheet, wssheet As Worksheet

Sheets("Regulares").Select

Columns("J:J").Select
    Selection.Insert Shift:=xlToRight
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "MO REAL"
    Columns("K:K").Select
    Selection.Cut
    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight

    Columns("Q:Q").Select
    Selection.Cut

    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight
    Range("K1").Select
    Selection.AutoFilter

    ActiveSheet.Range("A:Z").AutoFilter Field:=11, Criteria1:= _
        "INATIVE"
    Rows("5:5").Select
    Range("F5").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("A:Z").AutoFilter Field:=11
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],'DL List'!RC[-11]:R[32]C[-10],2,0)"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L5890")
    Range("L2:L5890").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("L2").Select
    ActiveSheet.Range("A:Z").AutoFilter Field:=11, Criteria1:="DL"
    ActiveSheet.Range("A:Z").AutoFilter Field:=12, Criteria1:="#N/A"
    Range("L23").Select
    ActiveCell.FormulaR1C1 = "DL"
    Range("L23").Select
    Selection.Copy
    Range("L25").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Range("A:Z").AutoFilter Field:=12
    Range("L4").Select
    ActiveSheet.Range("A:Z").AutoFilter Field:=11, Criteria1:=Array( _
        "G&A", "MOH", "IDL", "Other MOH"), Operator:=xlFilterValues
    ActiveSheet.Range("A:Z").AutoFilter Field:=12, Criteria1:="#N/A"
    Range("L7").Select
    ActiveCell.FormulaR1C1 = "IDL"
    Range("L7").Select
    Selection.Copy
    Range("L15").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("L7").Select
    ActiveWorkbook.Worksheets("Regulares").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Regulares").AutoFilter.Sort.SortFields.Add Key:= _
        Range("K1:K5890"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    ActiveSheet.Range("A:Z").AutoFilter Field:=12
    Range("K2").Select
    ActiveSheet.Range("A:Z").AutoFilter Field:=11
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("G2").Select
    Selection.Copy
    Range("J2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
    Range("J2").Select
    Application.CutCopyMode = False

End Sub

Solution

  • If you are counting the times "DL" and "IDL" occur...

    'Count DL and IDL

    count_DL = Application.WorksheetFunction.CountIf(ActiveSheet.Range("L:L"), "DL")
    
    count_IDL = Application.WorksheetFunction.CountIf(ActiveSheet.Range("L:L"), "IDL")
    

    'Paste results in Resultados sheet

    Worksheets("Resultados").Range("B17") = count_DL
    
    Worksheets("Resultados").Range("C17") = count_IDL