"If" statement using "And"

[Guest]

I am trying to use an "If" statement that also contains "And". Below is an
example of what I am trying to do:

Sub test()

If Sheets("Sheet 1").Range("A1").Value = "1" And _
Sheets("Sheet 1").Range("A2").Value = "Yes" Then
Sheets("Sheet 2").Select
ElseIf Sheets("Sheet 1").Range("A1").Value = "1" And _
Sheets("Sheet 1").Range("A2").Value = "No" Then
Sheets("Sheet 2").Select
Rows("1:2").Select
Selection.Delete Shift:=x1Up
End If

End Sub

Basically, if cell A1 says "1" I want it to select Sheet 2. And if cell A2
says Yes, I want it to leave Sheet 2 as is. If cell A2 says No, I want it to
delete a couple of lines in Sheet 2.

Can anyone please give me any pointers about what I am doing wrong.

Thanks heaps in advance!

 

[Leith Ross]

Hello Amanda,

You don't need the AND when sets are mutually exclusive. The only
effect A1 has is to select Sheet2. A2 will select Sheet2 if it is "No"
and then delete the lines you specfied. A1 has no effect on the outcome
of A2 and vice versa.

Sub test()

If Sheets("Sheet 1").Range("A1").Value = "1" Then
Sheets("Sheet 2").Select
End If

If Sheets("Sheet 1").Range("A2").Value = "No" Then
Sheets("sheet2").Select
Rows("1:2").Select
Selection.Delete Shift:=x1Up
End If

End Sub

Sincerely,
Leith Ross

 

[Dave Peterson]

Just a couple of comments/questions...

You're checking A1 to contain a string "1"--not the number 1. Is that on
purpose?

Second, are you sure you want to select "Sheet 2"? You don't usually have to
select a sheet to work on its ranges.

I think that this would do what you want:

If Sheets("Sheet 1").Range("A1").Value = 1 _
And Sheets("Sheet 1").Range("A2").Value = "No" Then
Worksheets("sheet 2").Rows("1:2").Delete
End If

And another option instead of using And is to use a nested if statement:

If Sheets("Sheet 1").Range("A1").Value = 1 Then
If Sheets("Sheet 1").Range("A2").Value = "No" Then
Sheets("sheet 2").Rows("1:2").Delete
End If
End If

What's nice about the nested if is you can add code that should be done no
matter what happens with that second If statement:

If Sheets("Sheet 1").Range("A1").Value = 1 Then
Worksheets("sheet 2").select 'if you really wanted to select
If Sheets("Sheet 1").Range("A2").Value = "No" Then
Sheets("sheet 2").Rows("1:2").Delete
End If
End If