upadate case statement

[oddworld]

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

 

[HalfAce]

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.

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.

 

[stanleydgromjr]

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

 

[stanleydgromjr]

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]

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?