Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"If" statement using "And"
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
"If" statement using "And"
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 -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=496314 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
"If" statement using "And"
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 Amanda wrote: 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! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if then else statement also using "and" | Excel Discussion (Misc queries) | |||
The "IF" Statement | Excel Worksheet Functions | |||
If statement | Excel Discussion (Misc queries) | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
How do I fix a circular reference in a financial statement? | Excel Discussion (Misc queries) |