Simple IF statement help

[excilcius]

Hello everyone,

I am trying to create an IF statement that says:

IF the value of C1>0, then set the value of 01=0; IF the value of O1>0, then set the value of C1=0. I have looked for various tutorials on IF statements, but they only affect one cell at a time. Wondering if I need to use VBA?

[Bob Phillips]

Is this VBA, or a formula?

If VBA, then just use


If Range("C1").Value > 0 Or Range("O1").Value > 0 Then

Range("C1").Value = 0
End If

If it is a formula, then you have a problem as you cannot have a formula that changes the value of the cell that the formula is in (C1).

[excilcius]

Thank you so much for the help. I have been looking for where to put this code, and I have come up that it would be an worksheet event, but I'm not quite sure where would I put this? I have opened up F11 and inserted the code for the worksheet, but nothing happens. I assume I'm just creating a macro, but I'm not quite sure.

[SamT]

What changes C1 and O1 when the VBA is not changing them? You need to know the trigger.

It looks like XLD hasn't had his full ration of tea yet.

[Bob Phillips]

Thank you so much for the help. I have been looking for where to put this code, and I have come up that it would be an worksheet event, but I'm not quite sure where would I put this? I have opened up F11 and inserted the code for the worksheet, but nothing happens. I assume I'm just creating a macro, but I'm not quite sure.

It would be something like this


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range("C1", "O1")) Is Nothing Then

If Target.Value > 0 Then

Me.Range("C1").Value = 0
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

[Paul_Hossler]

Are you asking about something like this logic?



Option Explicit

'IF the value of C1>0, then set the value of 01=0; IF the value of O1>0, then set the value of C1=0.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Target.Address = "$C$1" And Target.Value > 0 Then
Range("O1").Value = 0
ElseIf Target.Address = "$O$1" And Target.Value > 0 Then
Range("C1").Value = 0
End If
Application.EnableEvents = True

End Sub




Events have special calls, but the VBE will help

Select the sheet (or other code bearing container) and use the dropdowns on the top right to select the proper event and the VBE will add a skelton sub


It's also important to stop processing events so that the event handler doesn't call itself

15085

[excilcius]

Thanks so much everyone! Still a bit overwhelmed by all of this, but really appreciate the help.