If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Any idea why this is giving me a #Value! Error?
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 |
#2
|
|||
|
|||
Any idea why this is giving me a #Value! Error?
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 "msnyc07" wrote: 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 |
#3
|
|||
|
|||
Any idea why this is giving me a #Value! Error?
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" wrote: 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 |
#4
|
|||
|
|||
Any idea why this is giving me a #Value! Error?
Thanks Jacob, that seemed to do the trick
"Jacob Skaria" wrote: 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|