Comparing multiple variables to one string (at once)

Question

Is there any neater way (than presented below) to encode multiple AND comparison?

If a = "Not identified" And _
     b = "Not identified" And _
     c = "Not identified" And _
     c = "Not identified" And _
     d = "Not identified" And _
     e = "Not identified" And _
     f = "Not identified" And _
     g = "Not identified" Then

I have tried to google this for 20 minutes and found nothing. I expect a logical comparison encoded in one - two lines of code. Something like:

If WorksheetFunction.TextJoin("", True, a, b, c, d, e) = WorksheetFunction.Rept("Not identified", 5) Then

instead of multiple variables use a string array and loop through them setting a Boolean to false and breaking out of the loop when any item doesn't match. — Jeremy Thompson
– Jeremy Thompson, Commented Dec 29, 2022 at 11:13
Where from did you extract a to g? Are they in a range? Are they in an array? — FaneDuru
– FaneDuru, Commented Dec 29, 2022 at 11:13

FaneDuru · Accepted Answer · 2022-12-29 11:49:43Z

2

Please, try the next way:

Dim rng As Range, strText As String
   
   strText = "Not identified"
   Set rng = Range("C80:C87")
   If rng.cells.count = _
       WorksheetFunction.CountIf(rng, strText) Then Debug.Print "OK"

edited Dec 29, 2022 at 11:49

answered Dec 29, 2022 at 11:28

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dominik Dusza Over a year ago

If I understand correctly, your solution checks if any of strings in range = "Not identified" which would be an equivalent of "OR" comparison. I am looking for an equivalent of "AND" comparison.

FaneDuru Over a year ago

@Dominik Dusza What do you really want accomplishing? Forget about And for this very moment... Do you want checking if all involved cells contain "Not identified" string? If not, please better explain your need, not the way you think is appropriate... I tried only showing a way to identify such a string in a range.

Dominik Dusza Over a year ago

Do you want checking if all involved cells contain "Not identified" string? - YES. In my original inquiry I have posted a portion of code which is perfectly fine (the one with multiple ANDs). All I want to do is to write the same thing in a neater way.

FaneDuru Over a year ago

@Dominik Dusza OK. I will post a code line doing that... Replacing the part identifying if the string exists.

Collectives™ on Stack Overflow

Comparing multiple variables to one string (at once)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related