Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple "IF" statements every cell in range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple "IF" statements every cell in range
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple "IF" statements every cell in range
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple "IF" statements every cell in range
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple "IF" statements every cell in range
Thanks you two, works great! -- cbrd ------------------------------------------------------------------------ cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009 View this thread: http://www.excelforum.com/showthread...hreadid=500125 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple "IF" statements every cell in range
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple "IF" statements every cell in range
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" wrote in message ups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine text from multiple cells into one cell - =(A1&","&A2","&A3 | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming | |||
Multiple "Range" with "Cells" property? | Excel Programming | |||
Multiple "Range" with "Cells" property? | Excel Programming |