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

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

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

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

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

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

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,))))