Zero Out Column if another column has a certain qualifier

[zb14]

Hello,

I'm trying to write a code that would zero out any rows in the column "MatlDisc" if the data in the "Activity" column reads "Demo". I'd attach a picture of the spreadsheet but having trouble getting the forum to accept my question.

Thank you so much for your time and consideration on this one.

[Aussiebear]

It would be so much simpler if you uploaded a demo file.

[zb14]

It would be so much simpler if you uploaded a demo file.

Okay here is a demo file. 30076

[JKwan]

give this a try:


Option Explicit
Sub Main()
Dim WS As Worksheet
Dim LastRow As Long
Dim lRow As Long
Set WS = ThisWorkbook.Worksheets("Sheet1")
LastRow = FindLastRow(WS, "A")
For lRow = 2 To LastRow
If lRow = 9 Then Stop
With WS
If UCase(Left(.Cells(lRow, "AV"), 4)) = "DEMO" Then
.Cells(lRow, "AK") = 0
End If
End With
Next lRow
End Sub

Public Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
FindLastRow = WS.Range(ColumnLetter & Rows.Count).End(xlUp).Row
End Function

[p45cal]

if the data in the "Activity" column reads "Demo"None of them do!

[JKwan]

Not that I disagree.....

[zb14]

oof! My bad. "Demo" is short for "Demolition" but that was not made clear by me. Sorry about that! Thank you for looking into this for me.

[zb14]

JKwan this is a great start I really appreciate it. When I run it in mine, it stops the code at the "If lrow = 9 Then Stop" - Having trouble figuring out why. Am going to tinker with what you've given me for a bit. Appreciate the help really thankful!

[arnelgp]

Public Sub ZeroOutAK()
Dim i As Long
Dim arr As Variant
With Sheet1
arr = .Range("AV2:AV" & FindLastRow(Sheet1, "A"))
For i = 1 To UBound(arr)
'Debug.Print arr(i, 1)
If arr(i, 1) = "Demolition" Then
.Range("AK" & i + 1) = 0
End If
Next
End With
End Sub


' http://www.vbaexpress.com/forum/showthread.php?70171-Zero-Out-Column-if-another-column-has-a-certain-qualifier
' JKwan
'
Public Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
FindLastRow = WS.Range(ColumnLetter & Rows.Count).End(xlUp).Row
End Function

[JKwan]

JKwan this is a great start I really appreciate it. When I run it in mine, it stops the code at the "If lrow = 9 Then Stop" - Having trouble figuring out why. Am going to tinker with what you've given me for a bit. Appreciate the help really thankful!
The Stop line, you can just delete it. Left over code that I forgot to delete

[zb14]

arnelgp - This works great!!! Thank you so much! Impressed the boss with this one so thank you.

[zb14]

JKwan good to know thanks!