Determine if all items or elements are equal

  • Thread starter Thread starter Shawn
  • Start date Start date
S

Shawn

Does anyone have a good way in VBA to determine if every element in an array
is the same/equal? Or if each item in a range is the same/equal? ie. are
('one','one','one') equal? Yes. Or are ('one','one','two') equal? No.
 
Hi,

You could read through the array and check

Sub Sonic()
Dim ctrlArray As Variant
ctrlArray = Application.Transpose(Range("a1:a10").Value)
For x = 1 To UBound(ctrlArray)
If ctrlArray(x) <> ctrlArray(WorksheetFunction.Min(UBound(ctrlArray), x +
1)) Then
MsgBox "Array elements aren't the same"
GoTo getmeout
End If
Next
getmeout:
End Sub

Mike
 
Shawn said:
Does anyone have a good way in VBA to determine if every element in an array
is the same/equal? Or if each item in a range is the same/equal? ie. are
('one','one','one') equal? Yes. Or are ('one','one','two') equal? No.
Click to expand...

Hi. One idea to test if A1:A10 are the same.

Sub Demo()
Dim B As Boolean
B = WorksheetFunction.CountIf([A1:A10], [A1]) = 10
End Sub

= = = = =
HTH :>)
Dana DeLouis
 
Another way IF your array is a String array (which your example seems to
indicate), this method will not work with a non-String array is like this...

If Replace(Join(YourArray, ""), YourArray(LBound(YourArray))) = "" Then

if you want case sensitive equality (one <> One); or like this...

If Replace(Join(YourArray, ""), YourArray(LBound( _
YourArray)), "", , , vbTextCompare) = "" Then

if you want case insensitive equality (one = One)
 
Back
Top