CODEWITHSUNDEEP
vbaexcelexcel-2007
DannyBland
DannyBland

Reputation: 493

VBA If Countifs

I use the below to only instigate a certain piece of code if there is data,

If WorksheetFunction.CountIf(wksdata.Range("D:D"), "ASM001") > 0 Then

However, I need it to work under a CountIfs as well, as some sheets have more than one criteria, such as the example below where it uses BIR001, BIR004, BIR006, ITI001. I need it to continue if there is at least 1 of ANY of them.

If WorksheetFunction.CountIfs(wksdata.Range("D:D"), "BIR001", wksdata.Range("D:D"), "BIR004", wksdata.Range("D:D"), "BIR006", wksdata.Range("D:D"), "ITI001") > 0 Then

Can you help locate my error?

Upvotes: 1

Views: 36970

Answers (3)

DannyBland
DannyBland

Reputation: 493

I realised I should be adding 4 CountIf functions together.

If (WorksheetFunction.CountIf(wksdata.Range("D:D"), "BIR001") _
    + WorksheetFunction.CountIf(wksdata.Range("D:D"), "BIR004") _
    + WorksheetFunction.CountIf(wksdata.Range("D:D"), "BIR006") _
    + WorksheetFunction.CountIf(wksdata.Range("D:D"), "ITI001")) > 0 Then

Looks a bit messy but does the job!

Upvotes: 3

CallumDA
CallumDA

Reputation: 12113

This would be a good way to make it less messy:

Dim count As Integer

With Application.WorksheetFunction
   count = .CountIf(wksdata.Range("D:D"), "BIR001") + _
           .CountIf(wksdata.Range("D:D"), "BIR004") + _
           .CountIf(wksdata.Range("D:D"), "BIR006") + _
           .CountIf(wksdata.Range("D:D"), "ITI001")
End With

If (count > 0) Then

Upvotes: 3

user4039065
user4039065

Reputation:

This may be a chance to play with some square brackets but you shouldn't use the full column references.

If CBool([SUMPRODUCT(--(D1:D99999={"BIR001","BIR004","BIR006","ITI001"}))]) Then

Upvotes: 0

Related Questions