Any idea why this is giving me a #Value! Error?

[msnyc07]

Function FrstLtrs(MyStr As String) As String
Dim temp
Dim i As Long
TmpStr = Split(Trim(MyStr))
'MsgBox "String" + TmpStr
For i = 0 To UBound(TmpStr)
If Not (UCase(TmpStr) = "OF") And Not (UCase(TmpStr) = "FOR") And Not
(UCase(TmpStr) = "THE") And _
Not (UCase(TmpStr) = "AND") And Not (UCase(TmpStr) = "A") Then
If Asc(Left(TmpStr(i), 1)) >= 65 And _
Asc(Left(TmpStr(i), 1)) <= 90 Then
FrstLtrs = FrstLtrs & Left(TmpStr(i), 1)
End If
End If
Next
End Function

 

[Jacob Skaria]

Find the modified version..This will return only if the starting letters are
Caps...

Function FrstLtrs(MyStr As String) As String
Dim temp
Dim i As Long
TmpStr = Split(Trim(MyStr))

For i = 0 To UBound(TmpStr)
If Not (UCase(TmpStr(i)) = "OF" Or UCase(TmpStr(i)) = "FOR" Or _
UCase(TmpStr(i)) = "THE" Or UCase(TmpStr(i)) = "AND" Or UCase(TmpStr(i)) =
"A") Then
If Asc(Left(TmpStr(i), 1)) >= 65 And Asc(Left(TmpStr(i), 1)) <= 90 Then
FrstLtrs = FrstLtrs & Left(TmpStr(i), 1)
End If
End If
Next
End Function

If your requirement is to extract upper and lower case then use the UCASE
converstion at the beginning...as below

Function FrstLtrs(MyStr As String) As String
Dim temp
Dim i As Long
TmpStr = Split(Trim(UCase(MyStr)))

For i = 0 To UBound(TmpStr)
If Not (TmpStr(i) = "OF" Or TmpStr(i) = "FOR" Or _
TmpStr(i) = "THE" Or TmpStr(i) = "AND" Or TmpStr(i) = "A") Then
If Asc(Left(TmpStr(i), 1)) >= 65 And Asc(Left(TmpStr(i), 1)) <= 90 Then
FrstLtrs = FrstLtrs & Left(TmpStr(i), 1)
End If
End If
Next
End Function

 

[Jacob Skaria]

Try the below.....

Function GetAcronym(strData As String) As String

Const strExclude As String = "Of|for|the|and|a"
Dim intCount As Integer
Dim varData As Variant

varData = Split(Trim(strData))

For intCount = 0 To UBound(varData)
If InStr(1, "|" & strExclude & "|", "|" & varData(intCount) & "|", _
vbTextCompare) = 0 Then
If UCase(Left(varData(intCount), 1)) Like "[A-Z]" Then
GetAcronym = GetAcronym & Left(varData(intCount), 1)
End If
End If
Next

End Function

 

[msnyc07]

Thanks Jacob, that seemed to do the trick

Try the below.....

Function GetAcronym(strData As String) As String

Const strExclude As String = "Of|for|the|and|a"
Dim intCount As Integer
Dim varData As Variant

varData = Split(Trim(strData))

For intCount = 0 To UBound(varData)
If InStr(1, "|" & strExclude & "|", "|" & varData(intCount) & "|", _
vbTextCompare) = 0 Then
If UCase(Left(varData(intCount), 1)) Like "[A-Z]" Then
GetAcronym = GetAcronym & Left(varData(intCount), 1)
End If
End If
Next

End Function


--
Jacob

Function FrstLtrs(MyStr As String) As String
Dim temp
Dim i As Long
TmpStr = Split(Trim(MyStr))
'MsgBox "String" + TmpStr
For i = 0 To UBound(TmpStr)
If Not (UCase(TmpStr) = "OF") And Not (UCase(TmpStr) = "FOR") And Not
(UCase(TmpStr) = "THE") And _
Not (UCase(TmpStr) = "AND") And Not (UCase(TmpStr) = "A") Then
If Asc(Left(TmpStr(i), 1)) >= 65 And _
Asc(Left(TmpStr(i), 1)) <= 90 Then
FrstLtrs = FrstLtrs & Left(TmpStr(i), 1)
End If
End If
Next
End Function