Convert COUNTIFS formula to vba code so I can replicate

[vbastarterstar]

Hello,

I am trying to search for 4 certain phrases across multiple tabs, within certain date ranges

The formula I have used is

=countifs('Alex'!B20:B51,">1/1/18",'Alex'!B20:B51,"<8/1/18",'Alex'!C20:C51,"*TCM*")

B is my date column and C is my comment column

This formula works but I need to count how many times 'TCM', 'TCR', 'EMS' and 'EMR' appear in each of my tabs
I need a weekly count too

please can someone help me write the vba code I would need

 

[JackDanIce]

Re: Please help convert COUNTIFS formula to vba code so I can replicate

Welcome to the board.

Insufficient detail to solve I'm afraid, but start by trying:

Sub Loopy()

    Dim arr()   As Variant
    Dim temp    As Variant
    Dim x       As Long
    Dim str     As String
    
    str = "=COUNTIFS('Alex'!$B$20:$B$51,"">1/1/8"",'Alex'!$B$20:$B$51,""<8/1/18"",'Alex'!$C$20:$C$51,@Term)"
    arr = Array("""*TCM*""", """*TCR*""", """*EMS*""", """*EMR*""")

    With Worksheets.Add
        For x = LBound(arr) To UBound(arr)
            .Cells(1 + x, 1).Formula = Replace(str, "@Term", arr(x))
        Next x
    End With

    Erase arr
End Sub

It will output COUNTIFs for all 4 terms within the specified data range to the given sheet and range into a new sheet, starting in cell A1

 

[vbastarterstar]

Re: Please help convert COUNTIFS formula to vba code so I can replicate

Hi Jack,

Really appreciate that, thank you!

if I wanted to have the data count in a pre-made table,

how would I direct that?

 

[nikio8]

Re: Please help convert COUNTIFS formula to vba code so I can replicate

WorksheetFunction.SumIfs(

i found naming your ranges works easiest

 

[JackDanIce]

Re: Please help convert COUNTIFS formula to vba code so I can replicate

@vbastarerstar pre-made table? You'll need to be specific, provide sheet name, cell address of table and then be easier to figure out how to adapt the code to suit your needs