Comparing Text to Ensure Uniformity

[Labguy23]

The below function always evaluates as False even when I compare a cell to itself.

In addition, I wanted to be able to pull in all the strings as an array to make this easier manage, but I couldn't figure out how, so I had to use 20 seperate string variables. If you could shed any light on that, it would be helpful.

My goal is to have a formula that will look at n number of different values and return TRUE if they are all exactly the same or False if any one of the values are different.

Function CompareText(sText1 As String, sText2 As String, Optional sText3 As String, Optional sText4 As String, _
Optional sText5 As String, Optional sText6 As String, Optional sText7 As String, Optional sText8 As String, _
Optional sText9 As String, Optional sText10 As String, Optional sText11 As String, Optional sText12 As String, _
Optional sText13 As String, Optional sText14 As String, Optional sText15 As String, Optional sText16 As String, _
Optional sText17 As String, Optional sText18 As String, Optional sText19 As String, Optional sText20 As String) _
As Boolean

Dim iCount As Integer


iCount = 2

While "sText" & iCount <> Empty
If sText1 <> "sText" & iCount Then
CompareText = False
Exit Function
End If
iCount = iCount + 1
Wend

CompareText = True

End Function

 

[footej]

Not sure on the VBA side, but you can evaluate it in Excel...

A1 = Hi
B1 = Hi
C1 = Hi

D1 = "=AND(A11=B11,A11=C11,B11=C11)"
D1 = True

B1 = Hello
D1 = False

 

[Labguy23]

Well, the problem with that is this, I'm already using Vlookup to look across 18 different sheets (which would essentially mean 18 pairs of comparisons for a total of 36 Vlookups which I will end up calling twice in the formula due to 3 embedded if statements), because the values will not always be the same position (I have a seperate formula that checks to make sure the values I want actually do exist on each sheet). This leads to a seriously obnoxious formula that is basically unintelligible by all but the most sophisticated Excel users (of which I am one, but most people in my office aren't anywhere close to my level). If I can get this one simple function to work, I can immensely simplify the formula I would have to use.

In addition, if I can get this function to work, I can make it work regardless of how many sheets are involved without having to rewrite the formula each time. This is a workbook that will be used and reused over and over again with different numbers of sheets. I want to make it as simple as possible for myself to manage in the future.

 

[pgc01]

Hi Labguy23

This is an example without error checking:

Function CompareText(sStr1 As String, sStr2 As String, ParamArray vStrs() As Variant) As Boolean
Dim i As Integer

If sStr1 = sStr2 Then
    For i = 0 To UBound(vStrs)
        If vStrs(i) <> sStr1 Then Exit Function
    Next i
    CompareText = True
End If
End Function

Hope this helps
PGC

 

[Labguy23]

Perfect! Thank you very much.