Vba to return zero based on another cell text

[jaxs2009]

Excel 2007 VBA

Column AX range AX6:AX1000 when the text "CANCELLED" is present in any one row then enter the number "zero"in that row in column AZ

(The cells in column AZ are "accounting" formatted)

 

[Joe4]

Why VBA?
Why not just put formula:

=IF(AX6="CANCELLED",0,"")

in cell AZ6, and then copy that formula all the way down to row 1000?
If you want values and not the formula, you can do a Copy -> Paste Special -> Values afterwards.

Recording doing the steps above manually using the Macro Recorder would also give you the VBA code to do that.

 

[jaxs2009]

Why VBA?
Why not just put formula:

=IF(AX6="CANCELLED",0,"")

in cell AZ6, and then copy that formula all the way down to row 1000?
If you want values and not the formula, you can do a Copy -> Paste Special -> Values afterwards.

Recording doing the steps above manually using the Macro Recorder would also give you the VBA code to do that.

I am sorry I did not mention that in column AZ rows 6 through 1000 there is a number value in it, by entering the text "CANCELLED" in column AX rows 6 trough 1000 I want the current $ value to return to zero.

The =IF formula as is will work.

Column AX rows 6 through 1000 has a data validation list with the word "CANCELLED" in the list. That is why I thought of A VBA

 

[Joe4]

I am sorry I did not mention that in column AZ rows 6 through 1000 there is a number value in it

The devil is often in the details not mentioned!

Try this:

Sub MyMacro()

    Dim rng As Range
    Dim cell As Range
    
    Application.ScreenUpdating = False
    
'   Designate range to check
    Set rng = Range("AX6:AX1000")
    
'   Loop through range
    For Each cell In rng
'       Check to see if cell equals "CANCELLED"
        If cell.Value = "CANCELLED" Then
'           Zero out column AZ
            cell.Offset(0, 2).Value = 0
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[jaxs2009]

This works if you go to developer tab click on Macros and then manually run the macro, I want the marco or VBA to run automatically when the "CANCELLED" is selected in any cell AX6:AX1000 to zero out offset, 2 wwhich is column AZ same row selected, Thank you for your help.

 

[Joe4]

Put this code in the appropriate Sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("AX6:AX1000")) Is Nothing Then
        If Target = "CANCELLED" Then
            Application.EnableEvents = False
            Target.Offset(0, 2) = 0
            Application.EnableEvents = True
        End If
    End If

End Sub

 

[jaxs2009]

Worked great thanks very much, I can see what you did here.

 

[Joe4]

You are welcome.