Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
return used letters in a range function - an example
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
function like INDIRECT to return a range? | Excel Worksheet Functions | |||
I would like a function to return a value that fits its range | Excel Worksheet Functions | |||
return range of data from lookup function | Excel Worksheet Functions | |||
return range of data from lookup function | Excel Worksheet Functions | |||
VBA function to return letters of alphabet | Excel Programming |