A
aztecbrainsurgeon
no question here, just a custom function for the archive.
Function LettersUsedReturn(TrgtCellOrRange As Range) As String
'Evaluate a cell or range for the presence of standard English letters
and the letter type(s) used in the cell or range.
Dim myRng As Range
Dim myChars(11 To 36) As String
Dim iCtr As Long
Dim curWks As Worksheet
Dim myFormula As String
Dim LetterArray As String
'A to Z
For iCtr = 11 To 36
myChars(iCtr) = Chr(65 + iCtr - 11)
Next iCtr
With TrgtCellOrRange
Set myRng = Nothing
On Error Resume Next
Set myRng = TrgtCellOrRange
On Error GoTo 0
For iCtr = LBound(myChars) To UBound(myChars)
myFormula = "sum(len(upper(" & _
myRng.Address(external:=True) & "))" _
& "-len(substitute(upper(" & _
myRng.Address(external:=True) _
& "),""" & myChars(iCtr) & ""","""")))"
'
'Create the array of Letters whose count is > 0
If Evaluate(myFormula) > 0 Then
LetterArray = myChars(iCtr) + LetterArray
End If
Next iCtr
End With
If LetterArray = "" Then
LettersUsedReturn = "There are no letters present in current
target range."
Else
LettersUsedReturn = StrReverse(LetterArray)
End If
End Function
Search criteria:
test for presence of letters
return used letters in range
evaluate for presence of letter or letters
return an array of used letters
check for letters in a cell
used letters return
what letters are used in range or cell?
unique letters returned
extract unique letters
alphabetical order of letters in cell
what letters are present
Function LettersUsedReturn(TrgtCellOrRange As Range) As String
'Evaluate a cell or range for the presence of standard English letters
and the letter type(s) used in the cell or range.
Dim myRng As Range
Dim myChars(11 To 36) As String
Dim iCtr As Long
Dim curWks As Worksheet
Dim myFormula As String
Dim LetterArray As String
'A to Z
For iCtr = 11 To 36
myChars(iCtr) = Chr(65 + iCtr - 11)
Next iCtr
With TrgtCellOrRange
Set myRng = Nothing
On Error Resume Next
Set myRng = TrgtCellOrRange
On Error GoTo 0
For iCtr = LBound(myChars) To UBound(myChars)
myFormula = "sum(len(upper(" & _
myRng.Address(external:=True) & "))" _
& "-len(substitute(upper(" & _
myRng.Address(external:=True) _
& "),""" & myChars(iCtr) & ""","""")))"
'
'Create the array of Letters whose count is > 0
If Evaluate(myFormula) > 0 Then
LetterArray = myChars(iCtr) + LetterArray
End If
Next iCtr
End With
If LetterArray = "" Then
LettersUsedReturn = "There are no letters present in current
target range."
Else
LettersUsedReturn = StrReverse(LetterArray)
End If
End Function
Search criteria:
test for presence of letters
return used letters in range
evaluate for presence of letter or letters
return an array of used letters
check for letters in a cell
used letters return
what letters are used in range or cell?
unique letters returned
extract unique letters
alphabetical order of letters in cell
what letters are present