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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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?
 

Forum statistics

Threads
1,141,023
Messages
5,703,806
Members
421,318
Latest member
cg_cartoonexcel

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
Top