Nested IF statements in VBA

SunderlandUK

New Member
Joined
Mar 7, 2011
Messages
12
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Welcome to the forums!

Here are two possible solutions:

Code:
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
Code:
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Try like this

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

Forum statistics

Threads
1,141,018
Messages
5,703,756
Members
421,313
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top