Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatic CAPS
How do I format a cell to automatically CAPITALIZE a letter. E.G., I enter
a1 in the cell, and I want it to convert to A1. -- DSM |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatic CAPS
You can use this worksheet Change event code to do that. Right-click the
worksheet tab at the bottom and select View Code from the popup menu that appears. You will taken to the VBA editor when you do this... copy/paste the code below into the window that is displayed there... Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:B4")) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False Target.Value = UCase(Target.Value) End If Whoops: Application.EnableEvents = True End Sub Now go back to the worksheet itself and type some characters into the range of cells A1:B4... they will be automatically capitalized. Simply change the Range statement in the If-Then statement to cover the cells you want this functionality for. Rick "Dave" wrote in message ... How do I format a cell to automatically CAPITALIZE a letter. E.G., I enter a1 in the cell, and I want it to convert to A1. -- DSM |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatic CAPS
It worked! Thanks!
Another format question: Can I format a cell to prevent it from displaying "#N/A" when the reference cell does not yet contain any data? I've used the INDEX function to retrieve a value from another worksheet. DSM "Rick Rothstein (MVP - VB)" wrote: You can use this worksheet Change event code to do that. Right-click the worksheet tab at the bottom and select View Code from the popup menu that appears. You will taken to the VBA editor when you do this... copy/paste the code below into the window that is displayed there... Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:B4")) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False Target.Value = UCase(Target.Value) End If Whoops: Application.EnableEvents = True End Sub Now go back to the worksheet itself and type some characters into the range of cells A1:B4... they will be automatically capitalized. Simply change the Range statement in the If-Then statement to cover the cells you want this functionality for. Rick "Dave" wrote in message ... How do I format a cell to automatically CAPITALIZE a letter. E.G., I enter a1 in the cell, and I want it to convert to A1. -- DSM |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatic CAPS
Just wrap your formula with an IF statement that tests the reference cell
and displays "" if it is blank. For example, if your reference cell is A1, do this... =IF(A1="","",<<your formula goes here) Rick "Dave" wrote in message ... It worked! Thanks! Another format question: Can I format a cell to prevent it from displaying "#N/A" when the reference cell does not yet contain any data? I've used the INDEX function to retrieve a value from another worksheet. DSM "Rick Rothstein (MVP - VB)" wrote: You can use this worksheet Change event code to do that. Right-click the worksheet tab at the bottom and select View Code from the popup menu that appears. You will taken to the VBA editor when you do this... copy/paste the code below into the window that is displayed there... Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:B4")) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False Target.Value = UCase(Target.Value) End If Whoops: Application.EnableEvents = True End Sub Now go back to the worksheet itself and type some characters into the range of cells A1:B4... they will be automatically capitalized. Simply change the Range statement in the If-Then statement to cover the cells you want this functionality for. Rick "Dave" wrote in message ... How do I format a cell to automatically CAPITALIZE a letter. E.G., I enter a1 in the cell, and I want it to convert to A1. -- DSM |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatic CAPS
Alternative trap for a sample INDEX function formula
=IF(ISNA(INDEX(whatever)),"",INDEX(whatever)) Gord Dibben MS Excel MVP On Sun, 2 Mar 2008 10:02:01 -0800, Dave wrote: It worked! Thanks! Another format question: Can I format a cell to prevent it from displaying "#N/A" when the reference cell does not yet contain any data? I've used the INDEX function to retrieve a value from another worksheet. DSM "Rick Rothstein (MVP - VB)" wrote: You can use this worksheet Change event code to do that. Right-click the worksheet tab at the bottom and select View Code from the popup menu that appears. You will taken to the VBA editor when you do this... copy/paste the code below into the window that is displayed there... Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:B4")) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False Target.Value = UCase(Target.Value) End If Whoops: Application.EnableEvents = True End Sub Now go back to the worksheet itself and type some characters into the range of cells A1:B4... they will be automatically capitalized. Simply change the Range statement in the If-Then statement to cover the cells you want this functionality for. Rick "Dave" wrote in message ... How do I format a cell to automatically CAPITALIZE a letter. E.G., I enter a1 in the cell, and I want it to convert to A1. -- DSM |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatic CAPS
I altered my formula as recommended and the formula was deleted from the cell
(E4) and therefore defeats its purpose. I want the formula to remain but not display #N/A if D4 is blank. Thanks =IF(D4="","",(INDEX('C:\Users\Laptop 2\Desktop\[Donor List.xlsx]Donor Master list-08'!$B$1:$F$426, MATCH(D6,'C:\Users\Laptop 2\Desktop\[Donor List.xlsx]Donor Master list-08'!$B$1:$B$426,), MATCH("Sale Price",'C:\Users\Laptop 2\Desktop\[Donor List.xlsx]Donor Master list-08'!$B$1:$F$1,)))) -- DSM "Rick Rothstein (MVP - VB)" wrote: Just wrap your formula with an IF statement that tests the reference cell and displays "" if it is blank. For example, if your reference cell is A1, do this... =IF(A1="","",<<your formula goes here) Rick "Dave" wrote in message ... It worked! Thanks! Another format question: Can I format a cell to prevent it from displaying "#N/A" when the reference cell does not yet contain any data? I've used the INDEX function to retrieve a value from another worksheet. DSM "Rick Rothstein (MVP - VB)" wrote: You can use this worksheet Change event code to do that. Right-click the worksheet tab at the bottom and select View Code from the popup menu that appears. You will taken to the VBA editor when you do this... copy/paste the code below into the window that is displayed there... Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:B4")) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False Target.Value = UCase(Target.Value) End If Whoops: Application.EnableEvents = True End Sub Now go back to the worksheet itself and type some characters into the range of cells A1:B4... they will be automatically capitalized. Simply change the Range statement in the If-Then statement to cover the cells you want this functionality for. Rick "Dave" wrote in message ... How do I format a cell to automatically CAPITALIZE a letter. E.G., I enter a1 in the cell, and I want it to convert to A1. -- DSM |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatic CAPS
To save yourself a lot of typing just run this macro on the existing formulas to
add the NA trap. Sub NATrapAdd() Dim myStr As String Dim cel As Range For Each cel In Selection If cel.HasFormula = True Then If Not cel.Formula Like "=IF(ISNA*" Then myStr = Right(cel.Formula, Len(cel.Formula) - 1) cel.Value = "=IF(ISNA(" & myStr & "),""""," & myStr & ")" End If End If Next End Sub Gord On Sun, 2 Mar 2008 11:53:00 -0800, Dave wrote: I altered my formula as recommended and the formula was deleted from the cell (E4) and therefore defeats its purpose. I want the formula to remain but not display #N/A if D4 is blank. Thanks =IF(D4="","",(INDEX('C:\Users\Laptop 2\Desktop\[Donor List.xlsx]Donor Master list-08'!$B$1:$F$426, MATCH(D6,'C:\Users\Laptop 2\Desktop\[Donor List.xlsx]Donor Master list-08'!$B$1:$B$426,), MATCH("Sale Price",'C:\Users\Laptop 2\Desktop\[Donor List.xlsx]Donor Master list-08'!$B$1:$F$1,)))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fix all caps text to first letter caps | Excel Discussion (Misc queries) | |||
caps | Excel Discussion (Misc queries) | |||
How to change ALL CAPS to Initial Caps only? | Excel Worksheet Functions | |||
How can I convert all Caps to first letter caps in Excel? | Excel Worksheet Functions | |||
excel sheet all caps and needs to be only the first letter caps.. | Excel Discussion (Misc queries) |