CODEWITHSUNDEEP
vbaexcel
Reed Turgeon
Reed Turgeon

Reputation: 281

VBA countifs() w/ multiple criteria --> some text some numerical

My question is with regard to a VBA countifs() that is evaluating Alphabetical data in one column and numerical data in another. I have already used a multiple criteria countifs() in another part of this sheet. That formula looks like this: 

Filedresult = WorksheetFunction.CountIfs(range("n:n"), "TT", range("P:P"),   "Filed")
MsgBox Filedresult

Below is the filter code I need help with. I need the Countifs() filter to count the number of cells that:

StartYear in column W = 2017 AND in between Start Week & EndWeek = 1 & 4 OR have a WeekValue = 1,2,3 and 4

Dim StartYear       As Integer
Dim StartWeek       As Integer
Dim EndWeek         As Integer

'Setting Start Year, Start Week, and End Weeks
StartYear = WBsheetTwo.range("g5")
MsgBox StartYear
'THIS VALUE CURRENTLY ='s 2017

StartWeek = WBsheetTwo.range("g6")
MsgBox StartWeek
'THIS VALUE CURRENTLY ='s 1

EndWeek = WBsheetTwo.range("g7")
MsgBox EndWeek
'THIS VALUE CURRENTLY ='s 4

This is the filter I have attempted to build thus far based on the structure of the countifs() that I had previously built above.

I have reworded this post in the attempt to make my question more clear based on a readers request. Let me know if there is anything else I can clarify! Still new to the forum! Thanks for the help

****************************************FILTER*************************
'WEEK FILTER for # of TT Values
If WBsheetTwo.range("h6").Value = "YES" Then
MsgBox "Week Filter Is ON"

WBsheetOne.Activate
TTresultWFilter = WorksheetFunction.CountIfs _
    (range("n:n"), "TT", _
    range("w:w"), "&StartYear&", _
    range("x:x"), "1", _
    range("x:x"), "2", _ 
    range("x:x"), "3", _
    range("x:x"), "4")

MsgBox TTresultWFilter

Else
MsgBox "Week Filter if OFF"

'Pasting Origional TT Filter W/O Weeks Filter
WBsheetTwo.Activate
range("f12").Value = TTresult


****************************************FILTER*************************

Upvotes: 0

Views: 2819

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

Change 

TTresultWFilter = WorksheetFunction.CountIfs _
    (range("n:n"), "TT", _
    range("w:w"), " & StartYear & ", _
    range("x:x"), "1", _
    range("x:x"), "2", _ 
    range("x:x"), "3", _
    range("x:x"), "4")

To

TTresultWFilter = WorksheetFunction.CountIfs _
    (range("n:n"), "TT", _
    range("w:w"), " & StartYear & ", _
    range("x:x"), ">=" & StartWeek & ", _
    range("x:x"), "<=" & EndWeek & ")"

Upvotes: 1

Related Questions