Creating a New Column from Existing Column that pulls one word from a Description

[storocco1]

Hello! A little new to all this Macro, and VBA stuff so bear with me please.

I have a column that is labeled "Accident Description" and it is usually 1-2 sentences long describing an incident/accident. For example, "Rib injury when descending ladder and slipped and fell between 5 and 10 feet"

In that example I would want to pull the word "Rib" and add it to a new column titled "Body Part" and then I would like to add another column labeled "Source" and add the word "Ladder" to the source column.

I have tried:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "Rib" Then
Target.Value = "Rib"
End If
End Sub

But I am getting a run-time error '424' from it.

I will also have literally hundreds of these accident descriptions, and while I realize I will have to manually enter the words/descriptors I am looking for, I would like to know where to start!

 

[dreid1011]

Starting with your example, you can't just test the entire string to a single word like that. Perhaps something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If InStr(Target.Value,"Rib") Then
Target.Offset(,1).Value = "Rib"
End If
End Sub

This will test the value in the target cell and see if it contains the word "Rib" and if it does, then it sets the value of the cell in the next column to "Rib".

 

[storocco1]

Thank you for the quick reply! I am now getting a Compile Error: Expected End Sub

Here is the start of my code: The debugger is highlighting the first line Sub Claims_Analysis()

Sub Claims_Analysis()
'
' Claims_Analysis Macro
' Claims Analysis Steps
Columns("J:J").Select
Selection.AutoFilter
ActiveSheet.Range("$J$1:$J$2500").AutoFilter Field:=1, Criteria1:=Array( _
"PWC", "WCP", "WCV"), Operator:=xlFilterValues

' Add_Column Macro
Columns("K:K").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

' Macro to replace word in Accident Description Column

Private Sub Worksheet_Change(ByVal Target As Range)
If InStr(Target.Value, "Rib") Then
Target.Offset(, 1).Value = "Rib"
End If


End Sub

 

[dreid1011]

The way you have the entire piece set up is incorrect. You can't nest a sub within another like that. You can call a sub from another, but you can't nest them. It is expecting an "End Sub" statement before this line:

Private Sub Worksheet_Change(ByVal Target As Range)

This is what it is expecting (at least in regards to your current error):

Sub Claims_Analysis()
'
' Claims_Analysis Macro
' Claims Analysis Steps
Columns("J:J").Select
Selection.AutoFilter
ActiveSheet.Range("$J$1:$J$2500").AutoFilter Field:=1, Criteria1:=Array( _
"PWC", "WCP", "WCV"), Operator:=xlFilterValues

' Add_Column Macro
Columns("K:K").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

' Macro to replace word in Accident Description Column

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If InStr(Target.Value, "Rib") Then
Target.Offset(, 1).Value = "Rib"
End If


End Sub

 

[storocco1]

Ok I added that and it runs and creates a new column, but stops there. It doesn’t add the new word. I’m thinking I need to reference the new column but I’m not sure how to do it.