CODEWITHSUNDEEP
excelvba
user3812753
user3812753

Reputation: 79

Excel VBA Function: "AND" using text values

I am trying to get "TRUE" or "FALSE" from the Range and Criteria.

I have around 365 (year) columns withing rows 6 and 17 (11 values). Now between all of them I want to find if the range contains 8 specific values - if the range contains these 8 specific values I dont care about the rest 3, because containing those 8 would mean that on that day all the tasks will be done.

The specific values that must contain this range are: W1, W2, W3, W4, R1, R2, R3, R4. I am counting them as well to show how mane people will be on specific day, however it seems I am not able to make them mandatory and return me false or true answers. Here is how I am counting them:

Function empl(x As range) As Double

Application.Volatile

Dim xCell As range
Dim i As Double

i = 0

For Each xCell In x.Cells

    If Left(xCell.Value, 2) = "W1" Or _
       Left(xCell.Value, 2) = "W2" Or _
       Left(xCell.Value, 2) = "W3" Or _
       Left(xCell.Value, 2) = "W4" Or _
       Left(xCell.Value, 2) = "R1" Or _
       Left(xCell.Value, 2) = "R2" Or _
       Left(xCell.Value, 2) = "R3" Or _
       Left(xCell.Value, 2) = "R4" Then

            i = i + 1
    End If


Next xCell

empl = i

End Function

the problem using this logic is if I will make a mistake (and this is will be done for sure when arranging vacations) and I will input few W1, then count will not be the same as it has no problem counting same values again. I tried to use AND but it seems to return me no value at all, even if I fulfill the criteria in the range.

Any suggestions?

Thanks!

Upvotes: 0

Views: 59

Answers (3)

user3598756
user3598756

Reputation: 29421

I think you could simplify as follows:

Option Explicit

Function empl(x As Range) As Long
    Application.Volatile
    Dim key As Variant
    Dim i As Long

    For Each key In Array("W1", "W2", "W3", "W4", "R1", "R2", "R3", "R4")
        i = i + WorksheetFunction.CountIf(x, key & "*")
    Next key
    empl = i
End Function

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

It seems you are looking for a Unique Count of entries of your various strings. In VBA, you can do this with either the Dictionary or Collection object. The Collection object will return an error if you try to add a second item with the same key. (The Dictionary object also has the .Exists property, which can be used; but the Collection object is native VBA, so marginally easier to code).

As written, the routine is case insensitive. To make it case sensitive, remove Option Compare Text at the beginning

Using Collection (and your code):

Option Explicit
Option Compare Text

Function empl(x As Range) As Double

Application.Volatile

Dim xCell As Range
Dim col As Collection

Set col = New Collection

For Each xCell In x.Cells

    If Left(xCell.Value, 2) = "W1" Or _
       Left(xCell.Value, 2) = "W2" Or _
       Left(xCell.Value, 2) = "W3" Or _
       Left(xCell.Value, 2) = "W4" Or _
       Left(xCell.Value, 2) = "R1" Or _
       Left(xCell.Value, 2) = "R2" Or _
       Left(xCell.Value, 2) = "R3" Or _
       Left(xCell.Value, 2) = "R4" Then

            On Error Resume Next
                col.Add Left(xCell.Value, 2), Left(xCell.Value, 2)
            On Error GoTo 0
    End If


Next xCell

empl = col.Count

End Function

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

I'm not quite sure as to your main issue, but for what it's worth, I suggest looking into Select Case when you have that many If statements. I think it's a little easier to read and think through:

Function empl(x As Range) As Double

Application.Volatile

Dim xCell   As Range
Dim i       As Double

i = 0

For Each xCell In x.Cells
    Select Case Left(xCell.Value, 2)
    Case "W1", "W2", "W3", "W4", "R1", "R2", "R3", "R4"
        i = i + 1
    Case Else
        ' If it's NOT in the above list, do code that's here
    End Select
Next xCell

empl = i

End Function

Note: AFAIK, This function will return a Double (such as 5, if there are 5 matches), but your post says you're looking to do True or False. Why not create this function as Boolean and simply return True/False depending on the criteria? (I'm definitely missing something)

Upvotes: 0

Related Questions