clear contents in one column based on text in another column

[Barb Mick]

Hi,

If someone could help me? I'm looking for vba code to clear the contents of a cell in column A if the cell in column D has specific text.
ie If column D contains "Sample" then clear the contents of column A.

 

[Fluff]

How are the cells in col D being populated?

 

[Barb Mick]

I'm not sure that I understand the question. It's a text file dumped into excel that I'm trying to clean up unnecessary info and format.

I'm using this code to clear contents in one column if the cell in another is blank, but I need another line to clear contents if the cell has a certain word.

'(With rows 1 to 1000, if E is blank delete contents of column D)Columns("E:E").SpecialCells(xlCellTypeBlanks).Offset(, -1) = ""

 

[Fluff]

Ok, how about

Sub Barb_Mick()
   With Range("D1", Range("D" & Rows.count).End(xlUp))
      .Replace "Sample", "=xxxSample", xlWhole, , False, , False, False
      .SpecialCells(xlFormulas, xlErrors).Offset(, -3).ClearContents
      .Replace "=xxxSample", "Sample", xlWhole, , False, , False, False
   End With
End Sub

 

[Barb Mick]

Ohmigosh. That worked beautifully. Thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Barb Mick]

Ohmigosh. That worked beautifully. Thanks so much!

Could I possibly trouble you for one more line?

I need another line that clears the contents in column C if column D does not contain "TUBE".

 

[Fluff]

How about

Sub Barb_Mick()
   With Range("D1", Range("D" & Rows.count).End(xlUp))
      .Replace "Sample", "=xxxSample", xlWhole, , False, , False, False
      .SpecialCells(xlFormulas, xlErrors).Offset(, -3).ClearContents
      .Replace "=xxxSample", "Sample", xlWhole, , False, , False, False
      .Offset(, -1).Value = Evaluate("if(" & .Address & "<>""TUBE"",""""," & .Offset(, -1).Address & ")")
   End With
End Sub

 

[Barb Mick]

It works and you have made my week! Thanks again.

 

[Fluff]

Glad to help