Multiple "IF" statements every cell in range

[cbrd]

I have a column (B) and every cell from B4 to B4444 I wish to insert multiple IF statements like the following:

If I type "gm" (no quotes) into any cell from B4 to B4444, i wish for excel to change it to "General Motors"
If I type "am" (no quotes) into any cell from B4 to B4444, i wish for excel to change it to "American Motors"
If I type "ha" (no quotes) into any cell from B4 to B4444, i wish for excel to change it to "Honda America"
If theres any other text typed into the cell, excel leaves as is.

Any tips?

[Norman Jones]

Hi Cbrd,

Try:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, rng1 As Range
Dim rcell As Range

Set rng = Intersect(Target, Me.Range("B4:B4444"))

If Not rng Is Nothing Then
Application.EnableEvents = False
For Each rcell In rng.Cells
With rcell
Select Case LCase(.Value)
Case "gm": .Value = "General Motors"
Case "am": .Value = "American Motors"
Case "ha": .Value = "Honda America"
End Select
End With
Next rcell
End If
XIT:
Application.EnableEvents = True
End Sub

'<<=============

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.




---
Regards,
Norman


"cbrd" <[email protected]> wrote in message
news:[email protected]...
>
> I have a column (B) and every cell from B4 to B4444 I wish to insert
> multiple IF statements life the following:
>
> If I type "gm" (no quotes) into any cell from B4 to B4444, i wish for
> excel to change it to "General Motors"
> If I type "am" (no quotes) into any cell from B4 to B4444, i wish for
> excel to change it to "American Motors"
> If I type "ha" (no quotes) into any cell from B4 to B4444, i wish for
> excel to change it to "Honda America"
> If theres any other text typed into the cell, excel leaves as is.
>
> Any tips?
>
>
> --
> cbrd
> ------------------------------------------------------------------------
> cbrd's Profile:
> http://www.excelforum.com/member.php...o&userid=30009
> View this thread: http://www.excelforum.com/showthread...hreadid=500125
>

[Tom Ogilvy]

right click on the sheet tab and select view code. In the resulting module
put in code like

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sStr as String
If Target.count > 1 then exit sub
if not intersect(Target,Range("B4:B4444")) is Nothing then
sStr = Trim(lcase(Target.Value))
if len(sStr) <> 2 then exit sub
on Error goto ErrHandler
Application.EnableEvents = False
Select Case sStr
case "gm"
Target.Value = "General Motors"
case "am"
Target.Value = "American Motors"
case "ha"
Target.Value = "Honda America"
End Select
End If
ErrHandler:
Application.EnableEvents = True
End Sub

if you don't want the test to be case insensitive, change

sStr = Trim(lcase(Target.Value))

to

sStr = Trim(Target.Value)

--
Regards,
Tom Ogilvy



"cbrd" <[email protected]> wrote in message
news:[email protected]...
>
> I have a column (B) and every cell from B4 to B4444 I wish to insert
> multiple IF statements life the following:
>
> If I type "gm" (no quotes) into any cell from B4 to B4444, i wish for
> excel to change it to "General Motors"
> If I type "am" (no quotes) into any cell from B4 to B4444, i wish for
> excel to change it to "American Motors"
> If I type "ha" (no quotes) into any cell from B4 to B4444, i wish for
> excel to change it to "Honda America"
> If theres any other text typed into the cell, excel leaves as is.
>
> Any tips?
>
>
> --
> cbrd
> ------------------------------------------------------------------------
> cbrd's Profile:
http://www.excelforum.com/member.php...o&userid=30009
> View this thread: http://www.excelforum.com/showthread...hreadid=500125
>

[cbrd]

Thanks you two, works great!

[mcescher]

The other option that you could try would be
Tools-->Options-->Spelling-->AutoCorrect then fill the items into the
replace box. No code has to be entered, and it would happen instantly.

HTH,
Chris M.

[Bernie Deitrick]

You could use Tools / Autocorrect Options.... AutoCorrect tab, Check "Replace text as you type"
to change "gm" to "General Motors", etc.

However, I would use "amm" instead of "am", since you might possibly want to use the word "am" in
some other entry without having it change to American Motors.

HTH,
Bernie
MS Excel MVP


"cbrd" <[email protected]> wrote in message
news:[email protected]...
>
> I have a column (B) and every cell from B4 to B4444 I wish to insert
> multiple IF statements life the following:
>
> If I type "gm" (no quotes) into any cell from B4 to B4444, i wish for
> excel to change it to "General Motors"
> If I type "am" (no quotes) into any cell from B4 to B4444, i wish for
> excel to change it to "American Motors"
> If I type "ha" (no quotes) into any cell from B4 to B4444, i wish for
> excel to change it to "Honda America"
> If theres any other text typed into the cell, excel leaves as is.
>
> Any tips?
>
>
> --
> cbrd
> ------------------------------------------------------------------------
> cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009
> View this thread: http://www.excelforum.com/showthread...hreadid=500125
>

[Tom Ogilvy]

That is certainly an option if you don't mind it working anywhere in any
cell in excel (and probably want to change the string combinations to
something very unique).

--
Regards,
Tom Ogilvy


"mcescher" <[email protected]> wrote in message
news:[email protected]...
> The other option that you could try would be
> Tools-->Options-->Spelling-->AutoCorrect then fill the items into the
> replace box. No code has to be entered, and it would happen instantly.
>
> HTH,
> Chris M.
>