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

0
Utf
3/12/2010 1:23:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
1135 Views

Similar Articles

[PageSpeed] 37

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
> 
0
Utf
3/12/2010 7:27:04 AM
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
> 
0
Utf
3/12/2010 7:44:01 AM
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
> > 
0
Utf
3/12/2010 5:22:01 PM
Reply:

Similar Artilces:

"conditional formatting" "#values"
I have a formula that works fine but... some of the cells show #values, what i would like to do is conditional formatting them to white font if = #values. Any pointers? many thanks Try this: Select the cells to be impacted (I'll assume A1:A10, with A1 as the active cell) From the Excel main menu: <format><conditional formatting> Condition_1 Formula is: =ISERROR(A1) Click the [Formatting] button.....set the White font....Click the [OK] buttons. Does that help? *********** Regards, Ron XL2002, WinXP "Dewi..." wrote: > I have a formula that works fine bu...

What does this error message mean?
Hello, I gave my old computer to some novice friends, with Outlook Express on it. When trying to send/receive from Outlook Express I get the following dialog: WNetGetUser Returned and Outlook Express can't connect to the ISP. However, by using the Internet icon first and being already connected, Outlook Express sends and receives messages OK. I have checked the account settings I entered but I'm stuck as to why Outlook Express can't actually connect to the ISP itself. This newsgroup is for support of Outlook 97-2003 from the Office family for Windows PCs. For Outlook Ex...

Assign Macro to button in Excel doesnt work Any ideas?
I have followed the instructions in help but when I get to step 3 "right click to bring up shortcut menu & enter Assign Macro" the assign macro" option does not appear!! Any Ideas Hi Mike can you see assign macro - but it is greyed out? or is it not an option at all? if it is greyed out you'll notice around the button little diagonal lines, click on these and they should go furryish .. now try right mouse clicking. if the option is not there at all, please post back letting us know how you created the button. Cheers JulieD "Mike@Becketts" <Mike@B...

Filter for Same Value in Two Fields
There are two fields in my query. "From" and "To". For sake of ease, let's say both these fields are US Cities. How can I pull all the records that show shipped "From" Denver and that show shipped "To" Denver? Thank you in advance. You stated: shipped "From" Denver and that show shipped "To" Denver Technically this would mean both values were Denver which might not make much sense if you expected the From and To to be different. Assuming you are viewing your query in design view, you would type "Denver" und...

No Disk in Drive Error
I am using Money 2000, v.8, with Windows XP. For no reason when I try to access the program there is an MSMONEY.EXE message asking to insert a disk in drive D. By clicking the retry or continue button you can get into the program, but keep getting the message when switching from one function to another. I tried to reinstall, but when I insert the disk it just carries me into the program. Any help would be appreciated. Dick Bell You could try copying the CD to your Hard Disk. -- Regards Bob Peel, Microsoft MVP - Money Hints/Tips http://support.microsoft.com/default.aspx?scid=fh...

error msg to run inbox repair tool
Pointing to ext of .pst file and there are no .pst files in the system. Pointers seem to be off, error is giving me the path is not correct, do I reload Outlook? I cannot determine where the system is looking for the files, nor can I find where I modify the search path. I am currently running Outlook Express w/no problem. Problem seemed to have come up when downloading upgrades. My current ISP says it is Microsoft problem, any suggestions? ...

Error Message #21
Moneyplus 2007 Windows XP IE 7 This is the error message I get when I open money. "Money has detected that you did not close the file 'MSMONEY' before exiting." "Money now needs to verify the information in the file." It then asks me if I want to do this now, I click yes. The next message shows "The email address or password is incorrect please try again." This is a windows live ID that works on MSN. It seems my file is locked. I tried opening on a different computer, the same messages. I tried my back-up files, the same messages. I used the file Dec...

SUM for logical values
Using Excel 2002 SP3... If I type logical values into the formula, like this: =SUM(TRUE, TRUE, TRUE), my formula returns the value 3. If I reference cells containing logical values, like this =SUM(A1:A3), my formula returns 0. Does anybody know why there is a difference? -- tj Try: =A1+A2+A3 -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tjtjjtjt" <tjtjjtjt@discussions.microsoft.c...

mfc error
Microsoft is reporting an mfc error and I want to know how to delete the problem ...

Error message #54
I cannot delete anything from Outlook at all. When I try to delete I get "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." Try this link: http://support.microsoft.com/default.aspx?scid=kb;en-us;272227 Your pst may be damaged or too large. Mor Microsoft Messaging support 3+ years, frontline TSA1 "JGH" <anonymous@discussions.microsoft.com> wrote in message news:11a5601c3f575$4bc75800$a001280a@phx.gbl... > I cannot delete anything from Outlook at all. When I try > to delete I get "The messaging interfac...

error #16
Everytime I click on outlook 2003 to start the program I get this message and when I click yes, then it works fine. "Some Functionalbility of outlook connector has been disabled choosing yes will automaticly enable this functionalbility. It is just annoying. thanks gino which connector are you using? -- <stingraymmcm7@gmail.com> wrote in message news:1135709208.782972.146940@g14g2000cwa.googlegroups.com... > Everytime I click on outlook 2003 to start the program I get this > message and when I click yes, then it works fine. > > "Some Functionalbility of out...

Service Scheduling
We have recently installed CRM in our organisation and I am slowly going through customising each each section to suit our business. I have an issue with Service Activity, whereby when you go in and click schedule it goes through and allows you to select your resource/site details, and then when you click find resource it cannot run scheduling engine. Same issue occurs if you go in and try and create a task/appointment. It appears to be something with Exchange and CRM. However email tracking works and the exchange email router works fine. Just wondering if any body else has experieced this i...

Prevent cells with no value from being plotted on chart
I have a chart based on cells that are the result of a calculation. The cells show empty if the data used for the calculation is not yet available (future data). As a result, as my chart shows the whole year, the line drops and remains to zero after the last available set of data. Excel will let me prevent an empty cell of being plotted but since there is a fornmula is the cell, it is not considered empty, and therefore displayed. How can I prevent these cells to be plotted on the chart? Thanks Vince You can use an IF formula with NA(). For example: =IF(B2="",NA(),B...

I Keep getting error message
Hello, When using Outlook 2002 I get a box popping up on my screen every few minutes saying: "Unable to update public free/busy data. Operation failed" I recently set up a second data file and successfully created a rule to divert emails from a domain name I have to that data file and loaded a bunch of appointments into the calendar of that data file. Is this popup to do with that? Also, I think this may only happen when my Dialup internet connection is connected Thanks See my reply to your earlier post. -- Jocelyn Fiorello MVP - Outlook *** Replies sent to my e-mail address...

Can't open Outlook for Web Access over the net using 2000 or XP but can with 98?? Any ideas
Hi there, Can anyone tell me why a 2000 or XP system cannot log on remotely to Exchange's Outlook (2000 Server) for web access over https: when a 98 system can no-problem? Thx in advance. "David Sullivan" <dsullivan@bcl-international.com> wrote in message news:lGNLc.5216$Z14.6537@news.indigo.ie... > Hi there, > > Can anyone tell me why a 2000 or XP system cannot log on remotely to > Exchange's Outlook (2000 Server) for web access over https: when a 98 > system > can no-problem? > > Thx in advance. > > What version of IE is running ...

Array Constant: How do I reference each value in a formula
I have an array constant {1000,1.2,2%} in cell A1 S = 1000 P = 1.2 R = 2% How do I reference the array constant to create this formula: =(S/P)-((S/P)*R) Hi, I can't duplicate your entry. First, to be an array it must be entered starting with an = for example ={1,2,3} Second, you show the last argument as 2% - Excel won't accept the % in and array entry. Maybe you are typing {1000,1.2,2%} into the cell? If so, it is not an array, its just a bunch of text. You could enter it as ={1000,1.2,0.02} -- Thanks, Shane Devenshire "notso" wrote: > I have an array co...

merge multilple data values
how would I merge multilple data values: in seperate columns as: js-1234 to .jpg Thanks Hi Jennifer, ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Hi Jennifer, Try using =(A1&B1) where A1 and B1 are the cells where your data sits ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ X-No-Archive: yes "Jennifer Burnel" <jenny@...

PJS2003
We have a couple plans that have their actual work values matching in the portfolio analyzer view. They stopped updating. They seem to be plans that were "saved as" another plan and then republished under the new name. Once the "Save as" another plan was completed, the olap work value stayed the same as the original plan's work value and did not continue updating with actual work reported. Is there a way to detached the "saved as" plan from it's original values in the OLAP cube? Example Project A has 5 hours total in Work on the Portfo...

Store Procedure Error
I changed a table relationship for a customized Report and after exiting the sytem and reentering Great plains, I got the error below: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'zDP_SY01401F_1'. [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'SY01401'. Any ideas? Thanks! Matt ...

Inventory Values Copied from Old into New Items
Guys, When I use the Copy function to create new items from older items, the invenory values are carried over. In other words, the On Hand, Committed... field values under the Inventory tab are the same as the source item. Now, this cannot be correct behaviour, since a new item is independent onto its own, with its inventory quantities totally unrelated to the item from which it was originally copied. Anybody has come across this before? How can I set this right? Thanks, This is a multi-part message in MIME format. ------=_NextPart_000_0053_01C92EF5.0E1EBB10 Content-Type: text/plai...

Automatically fill empty cell with default value i.e. 0
Hi everyone, Example: Name Qty Cost Total ABC 10 1.00 10.00 BC 0.00 AC 10 2.00 20.00 C 2 1.00 2.00 I would like to use a macro to auto fill up the "Qty" and "Cost" field with 0 (zero) if they are blank or empty. May I know how to achieve it? Thank you. altric wrote: > Hi everyone, > > Example: > > Name Qty Cost Total > ABC 10 1.00 10.00 > BC ...

Error 2237
Hi! I have a form that is having some coding issues. When I use the copy record command I get run time error 2237. I deleted the lookup box on my form and tried the copy record button and had no problems. When I reinserted the lookup box, the error resumed. Here is all of the code on the form: Option Compare Database Private Sub Form_Current() If Me.NewRecord Or IsNull(Me.prjParentProjectID) Then Me.prjParentProjectID.Visible = False Else Me.prjParentProjectID.Visible = True End If End Sub Private Sub cmdClose_Click() On Error GoTo Err_cmdClose_Click DoCmd.Close...

keep formula after typing in a value in a excel cell
Hi there, my problem is, that i need something like: there is a function in a excel-cell (for example '=setvalue("sqlserver", "DB", "table")'). now i type in a value (for example 100) then the function "setvalue" must write this value (100) in the server, db and table from the parameters. after leaving the cell the formula is still the same and only the value 100 ist visible for the user. and if i go back to the cell than i can see the function in the menubar. there is a product from applix (TM1) and they did it. the problem is, that the souce...

Error closing Outlook
I am running Office XP on a system running Win XP Pro with 512MB of RAM and an AMD XP 1800+ CPU. I have the latest service packs and patches for Office and XP. Every time I close Outlook, I get the following error message: OUTLOOK.EXE - Application Error The instruction at "0x6601cd49" referenced memory at "0x000000a0". The memory could not be "read". Click on OK to terminate the program. I had this error before I upgraded my RAM and motherboard and after I upgraded. I do not get this error with any other program. Does anyone have any idea what t...

UPDATEBLOB error
OS: Windows XP. DBMS: SQL Server 2008. Development tool: PowerBuilder 10.5. I am attempting to save a digital photo image to a table ( datatype of column is varbinary(max) ) with an UPDATEBLOB statement via embedded SQL; however, I'm getting the following error - SQLSTATE = S1010 [Microsoft][ODBC Driver Manager] Function sequence error. (SQLCODE = -1 and SQLDBCODE = 999). Does anyone have any suggestions to help solve this problem? In addition, the connection to SQL Server 2008 is via ODBC. The DBPARM = "Connectstring='Driver=SQL Server Native Client 1...