upadate case statement

oddworld

Board Regular
Joined
May 31, 2005
Messages
248
hi all i am sure this is simple i just can't nake this happen.

What i am trying to do is look in column d at a series of date and if the following data is found "any holden" or "any ford" or "any mazda" is found i would like "any holden" turned into "holden" in column e, and the same for "any ford" becomes "ford, in the corressponding row in column "e".
I could do this using trim or left or right formula, however this sheet will regulary updated so i don't want the user to worry about fomula. i would like a case statement of some sort if possible.

any advice would be appreciated
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hello oddworld,
If I understand your situation then this should give you a start.
Just change the sheet name to suit. Can be run from any sheet.
Code:
Option Explicit
Option Compare Text
Sub Demo()
Dim LstRw As Long, i As Long
LstRw = Sheets("Sheet1").Cells(Rows.Count, "D").End(xlUp).Row
  With Sheets("Sheet1")
    For i = 1 To LstRw
      Select Case .Cells(i, "D").Value
        Case "any holden"
          .Cells(i, "E").Value = "holden"
        Case "any ford"
          .Cells(i, "E").Value = "ford"
        Case "any mazda"
          .Cells(i, "E").Value = "mazda"
          
        'Any other cases you require
        
      End Select
    Next i
  End With
End Sub

[EDIT:]
If there are more 'anys' you'll be looking for and you don't want to list them all
by name post back and this can be made to be more dynamic.
 
Joined
Jul 30, 2006
Messages
3,656
oddworld,

The easiest way is to use a formula:
=MID(D2,5,LEN(D2)-4)

If cell D2 contains "any holden", the above formula in cell E2 would display "holden".

Another solution is to use the Worksheet_Change event:

Copy the below code:

'----------Code Begins Here--------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
' Macro created 09/24/2006 by Stanley D. Grom, Jr.
'
Dim strCellValue As String
Dim strNewCellValue As String
Dim intCellRow As Integer

If Not Intersect(Target, Range("D:D")) Is Nothing Then
Cancel = False 'Do nothing

If ActiveCell.Value = "" Then
intCellRow = ActiveCell.Row
Range("E" & intCellRow & "") = ""
Else
strCellValue = Trim(ActiveCell.Value)
intCellRow = ActiveCell.Row
strNewCellValue = Mid(strCellValue, 5, Len(strCellValue) - 4)
Range("E" & intCellRow & "") = strNewCellValue
End If
End If

End Sub
'----------Code Ends Here----------------------------------


Press the 'ALT' and 'F11' keys, and paste the above code into the VBA Project(your spreadsheet name.xls), Sheet1(Sheet1)


I am using Windows XP Professional SP2, and Excel 2003 SP2.

Have a great day,
Stan
 
Joined
Jul 30, 2006
Messages
3,656
oddworld,

Follow the my previous instructions, but use this code:

'----------Code Begins Here--------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
' Macro created 09/24/2006 by Stanley D. Grom, Jr.
'
Dim strCellValue As String
Dim strNewCellValue As String
Dim intCellRow As Integer

If Not Intersect(Target, Range("D:D")) Is Nothing Then
Cancel = False 'Do nothing

If ActiveCell.Value = "" Then
intCellRow = ActiveCell.Row
Range("E" & intCellRow & "") = ""
Else
strCellValue = Trim(ActiveCell.Value)
intCellRow = ActiveCell.Row
If Len(strCellValue) < 5 Then
Exit Sub
End If
strNewCellValue = Mid(strCellValue, 5, Len(strCellValue) - 4)
Range("E" & intCellRow & "") = strNewCellValue
End If
End If

End Sub
'----------Code Ends Here----------------------------------


Have a great day,
Stan
 

oddworld

Board Regular
Joined
May 31, 2005
Messages
248
re

thanx for that it worked a treat.
one last question, i have a multi field in some cases ie in some cells if have holden/ford/mazda, what i need to to is apply a % to the number of occurences.

ie if i have 10 cells with holden/ford/mazda in column "d" ,
i would apply 14% to holden 34% to ford and 52% to mazda.
so in column "e" there would be approx 1.4 holden, 3.4 ford, and 5.2 occurances. how do i write a statement to do this and how do i write the statement to be in whole parts no in decimal point, should it be rounded up or down?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,953
Messages
5,545,153
Members
410,666
Latest member
forzasec
Top