How to connect the code in excel sheet

Question

Can anybody help me, I'm trying to find out the age band of listed codes, using the VBA CountIfs function with the data shown below? .

Age band of the codes:

Answer 1

^{(See below to download sample file.)}

You could do it easier without VBA like this:

The formula in E4 is:

=COUNTIFS($A$1:$A$15,"="&$D4,$B$1:$B$15,">=" &E$2,$B$1:$B$15,"<="&E$3)

...but if you need to do this in VBA for some reason, you can do basically the same thing with a few syntax changes:

This would give the same value as E4:

MsgBox Application.WorksheetFunction.CountIfs(Range("A1:A15"), Range("D4"), _
    Range("B1:B15"), ">=" & Range("E2"), Range("B1:B15"), "<=" & Range("E3"))

---

Or, instead of specifying the ranges directly in the formula (like if you were planning on looping through the cells or specifying individuals):

Sub test()
    Dim ws As Worksheet
    Dim lowerLimit As Range, upperLimit As Range, findName As Range
    Dim rangeNames As Range, rangeValues As Range

    Set ws = Worksheets("Sheet1")
    Set lowerLimit = ws.Range("E2") 'or specify actual values instead of "Range"'s
    Set upperLimit = ws.Range("E3")
    Set findName = ws.Range("D4")
    Set rangeNames = ws.Range("A1:A15")
    Set rangeValues = ws.Range("B1:B15")
    MsgBox Application.WorksheetFunction.CountIfs(rangeNames, findName, rangeValues, _
        ">=" & lowerLimit, rangeValues, "<=" & upperLimit)
End Sub

---

Download Sample File:

• You can view sample file used above on JumpShare here, but COUNTIFS and VBA is not supported in the online viewer.

• The Jumpshare page also has a download link, or direct link here. Note that the file contains VBA so you may get an "unknown code" (etc) warning.

---

More Information:

• Office.com : COUNTIFS Function (Excel)

• Excejet : Excel COUNTIFS Function

• MSDN : WorksheetFunction.CountIfs Method (Excel/VBA)

• Stack Overflow : Count unique values in a column in Excel