Nested IF statements in VBA

[SunderlandUK]

Hi

I'm new to Excel VBA and I'm trying to teach myself

The problem I haves is this

I am trying to write a macro that will action on 2 cells: E14 and E15

If E14 = 1 or 3 or 5, then clear its contents and assign a 0 to E15
If E15 = 1 or 3 of 5, thenclear its contents and assign a 0 to E14
Any other number means no action

I have tried various variations of code - the latest being:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 03/03/2011 by
If Range("E14").Value = 1 Or 3 Or 5 Then

Range("E15").Value = 0
Range("E14").Select
Selection.ClearContents
Range("E15").Select


ElseIf Range("E15").Value = 1 Or 3 Or 5 Then
Range("E14").Value = 0
Range("E15").Select
Selection.ClearContents
Range("E14").Select
End If
End Sub

But it doesn't work properly

Any help would be gratefully accepted

PS aplogies if this message is in the wrong format/style

 

[MrKowz]

Welcome to the forums!

Here are two possible solutions:

Public Sub SunderlandUK()
Select Case Range("E14").Value
    Case 1, 3, 5
        Range("E15").Value = 0
        Range("E14").ClearContents
    Case Else
End Select
Select Case Range("E15").Value
    Case 1, 3, 5
        Range("E14").Value = 0
        Range("E15").ClearContents
    Case Else
End Select
End Sub

Public Sub SunderlandUK2()
If Range("E14").Value = 1 Or Range("E14").Value = 3 Or Range("E14").Value = 5 Then
    Range("E15").Value = 0
    Range("E14").ClearConstants
ElseIf Range("E15").Value = 1 Or Range("E15").Value = 3 Or Range("E15").Value = 5 Then
    Range("E14").Value = 0
    Range("E15").ClearContents
End If
End Sub

 

[VoG]

Try like this

If Range("E14").Value = 1 Or Range("E14").Value = 3 Or Range("E14").Value = 5 Then

 

[SunderlandUK]

Thank you Mr Kowz

Both solutuions worked a treat

SunderlandUK

 

[SunderlandUK]

Thanks VoG