delete cell content based on criteria in another column

[RinaHale]

Hello!
I am making my very first macro so I am completely new at this
.
I need to delete the value DEB from column BY if the value of the cell in column AJ contains 14 somewhere in the alphanumeric value. That is all I need to do.

But my data base is huge (A to GW) and the number or rows varies and grows every month - as it stands now I have a little under 30K rows.

DEB is not unique in column BY; by which I mean values without 14 can have a DEB, but that is ok and needs to stay in the data base.

Can someone, please, help me? I am driving myself mad.
Thank you so much!

 

[Fluff]

Hi & welcome to the board.
How about

Sub ClearDEB()
With Range("By2", Range("BY" & Rows.Count).End(xlUp))
   .Value = Evaluate(Replace(Replace("if((@=""DEB"")*(isnumber(find(""14"",@a))),"""",@)", "@a", .Offset(, -41).Address), "@", .Address))
End With

End Sub

 

[RinaHale]

Hi Fluff,

Thank you for your reply.
I think your code replaces the DEB with blank, right?
I need the cell to be empty as I import the result in a system which considers blank as a value. It's over my head why blank would be considered a value, but...
Do you think it would be possible to clear the cell completely?

Thank you again!

 

[Fluff]

The code should be making the cell empty, if it's causing you problems try

Sub ClearDEB()
With Range("By2", Range("BY" & Rows.Count).End(xlUp))
   .Value = Evaluate(Replace(Replace("if((@=""DEB"")*(isnumber(find(""14"",@a))),"""",@)", "@a", .Offset(, -41).Address), "@", .Address))
   .Value = .Value
End With

End Sub

 

[RinaHale]

this code is giving me a zero in BY
maybe I am doing something wrong...

 

[Fluff]

Do you have any blank cells in that column?

 

[RinaHale]

Hi Fluff,

The column does not have blank cells in it. But blank is an accepted value.

But. Good news. I used your second coding and it now gave me a blank cell. I have sent it to the system and am now waiting for the result. I hope for the best.

Thank you so much!