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

M

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
 
J

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
 
J

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
 
M

msnyc07

Thanks Jacob, that seemed to do the trick

Jacob Skaria said:
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


msnyc07 said:
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
 

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