return used letters in a range function - an example

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top