I need help with a cell formula

mrbrownsfan

Board Regular
Joined
Feb 9, 2006
Messages
76
I want to make a certain cell, (H7) to convert the letters Y or N to display the word Yes or No. Is this possible? Is it better to create a macro to do this or to use a cell formula or VBA?

For example: To make filling out the spreadsheet quicker my boss just wants to type the letter "Y" in a cell and have it change to the word yes.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Put this in the Worsksheet Change Event for the sheet you wish this to happen.

Code:
Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("H7") Then
        If Target = "Y" Then Target = "Yes"
        If Target = "N" Then Target = "No"
    End If
End Sub
 
Upvote 0
One simple alternative may be to use Data Validation, choosing the List options, and setting "Yes;No" as your options.
Then they can just select the appropriate values from the drop-down.
And if they are all in a single-column, once you have select "Yes" or "No" once, you can just type in the first letter of each one, and it will autocomplete it.

Otherwise, you would probably want to use some automated VBA code that converts "Y to Yes" and "N to No" upon data entry.
If you want to go that route, then please let us know what range these entries will be in (I assume it is more than just H7).
 
Upvote 0
Thank you both so much for your help. Yes, It will be the range of H7 through H36. I will try both options to see which works best for him.
 
Upvote 0
Here is event procedure VBA code that will work on the entire range (H7:H36), and will handle it correctly regardless of whether they enter upper or lower case letters.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim isect As Range
    Dim cell As Range
    
    Set isect = Intersect(Target, Range("H7:H36"))
    
    If Not isect Is Nothing Then
        Application.EnableEvents = False
        For Each cell In isect
            Select Case UCase(cell)
                Case "Y"
                    cell = "Yes"
                Case "N"
                    cell = "No"
            End Select
        Next cell
        Application.EnableEvents = True
    End If
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top