"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!

0
Amanda (82)
12/28/2005 2:57:02 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
278 Views

Similar Articles

[PageSpeed] 8

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?action=getinfo&userid=18465
View this thread: http://www.excelforum.com/showthread.php?threadid=496314

0
12/28/2005 3:30:32 AM
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
0
petersod (12004)
12/28/2005 3:02:13 PM
Reply:

Similar Artilces:

(MFC) How to use messages to communicate between CMDIFrameWnd and CScrollView?
Alright, so I tried to use AfxGetMainWnd() from inside a CScrollView-derived class to access a function inside of a CMDIFrameWnd-derived class. It worked under the "Debug" configuration, but I'm getting a nasty error under the "Release" configuration: Coil error LNK2019: unresolved external symbol "public: void __thiscall CMainFrame::setCurrRef(class ATL::CStringT<char,class StrTraitMFC<char,class ATL::ChTraitsCRT<char> > >)" (?setCurrRef@CMainFrame@@QAEXV?$CStringT@DV?$StrTraitMFC@DV?$ChTraitsCRT@D@ATL@@@@@ATL@@@Z) referenced in function &...

Does an if statement calculate both the true and false?
I have a spreasheet, with 40000+ rows. I have an if statement which is basically if(A1="OK",0,long and complex formula). Almost all instances are "OK" with a few exceptions requiring the complex, time consuming part of the formula. Does excel calculate that part of the formula even for cells which are "OK" and hence taking up loads of time, or does it ignore it knowing it doesn't need to return it? Thanks! Chris Hi, VBA evaluates both parts of the formula, the Excel worksheet IF function only evaluates one part of the formula depending on...

Can I put "OR" in a IF(AND statement?
Hi. I�m using this expression on a worksheet. =IF(AND(A13="p",C13="small",E8<11),"1 km","n/a") There are 7 different possible entries I need to put in C13 but I can� figure out how to express it � consequently I had to group them into th two categories �small� and �large�. What I�d like to be able to do is to modify this so I can put th �raw�data in � so I can say something like IF(AND(A13=�p�, c13 = �a� o �b� or �c� or �d�, E8 <11), �1 km�, �n/a�. Of course the next thing I need to do is for the other possible entrie � IF(AND(A13=�p�, ...

dial a telephone number using the modem,and playing an audio file when the person lifts up telepone
I wanted to make a vb application which would dial a telephone number using the modem. When the person on the other end lifts up the telephone (answers the phone), the application should start playing an audio file(eg. .wav file). The approach I took was using the Tapi. As my modem is connected to the serial port on my computer I send AT commands to make the phone call . This is working fine and the call is being placed. I wanted to know whether this is possible at all? Whether I am approaching the wrong way? If it is possible, then please give some suggestions or samples(vb or vc). If it is...

The diference betweein using combo from list box and table
Hello, Before I go further in desining my form, what is best way to update table using combo box whether to use listbox or from table. when I see the address sample of database the " Role" is taken from Role table, Is not more simple if we make it as a listbox to update table? Thanks in advance -- H. Frank Situmorang List boxes are usually used to display or choose data in a form. Combo boxes are used for searching, display, and for data entry on a form. Always do the data entry from a form where you can construct rules and code to verify the data. Never enter data directly...

Nested If statements and lookup function
I have two cells that I need to check before I can lookup a value. I thought I did it right with nested if's and then lookup within the 'true' part of the if, but it's not working. Here's what I got: 1) Check the first cell (a1) for 4,6,8,10 or 12; 2) Then check the second cell (a2) for 15,20,25,30,35,40,45,50,55,60,65,70,75 or 80; 3) Then based on the second cell's value (a2) go lookup the appropriate value in a table (b1:c14): 15 100 20 190 25 290 30 400 35 520 etc. etc. I tried this: =if(a1=4,lookup(a2,b1:b14,c1c14),if(a1=6,lookup (a2,b1:b14,c1c14),i...

Begin and End Block in an IF statement
I have seen in a couple of places that the use of begin and end block in a single statement is discouraged Outside of extra 2 lines of code, is there an issue in enclosing a single statement with begin and end block? Thanks in advance RG (nobody@nowhere.com) writes: > I have seen in a couple of places that the use of begin and end block in a > single statement is discouraged > Outside of extra 2 lines of code, is there an issue in enclosing a single > statement with begin and end block? No. My personal style is that if the statement is a single line, I do...

Using the IF Command
Hi I am trying to automatically place Members in a Group depending on their Score, i.e If the Score is a value from 1 to 13 then Group A, If the Score is a value from 14 to 26 then Group B, If the value is a value from 27 to 39 then Group C. My Worksheet looks like this :- Column A B C Name Score Group J Bloogs 6 A J Brown 10 A J Smith 29 C I have been playing with this for ages and have now blown my brain, I would greatly appreciate any help Joe Try this in C2: =3DIF(B2<=3D13,"A",IF(B2<=3D26,"B",...

if and and statements
Looking for help with a 2 x 4 matrix with the following conditions A1 B1 C1 Answer 3 0 1 A1*4+2 3 0 2 0 3 1 1 A1*2+1 3 1 2 0 =IF(AND(B1=0,C1=1),+A1*4*2,IF(AND(B1=0,C...

Is it possible to run more than one macro using one command button? if yes, how??
Hello, I had many macros that i want to run them all at once but not upon the opening of the excel workbook. i want to have one command button that runs them all. is it feasible?? Samer Gamal Hi Create another Macro that calls each other macro in turn. Sub RunAll() Call Macro1 Call Macro2 .. .. .. Call MacroX End Sub -- Regards Roger Govier <samergadelrab@gmail.com> wrote in message news:d57632d6-d468-4a71-b8f6-c9a87ce9150e@p25g2000hsf.googlegroups.com... > Hello, > I had many macros that i want to run them all at once but not upon the > opening of the excel workbook. i ...

using AND in a countif formula
I am trying to make a formula that gives me a count of how many row have the same date and time...i have tried everything i can think o and can't seem to get it...any help is appreciated! i.e. saturday 14:00 saturday 11:00 saturday 14:00 saturday 14:00 saturday 14:00 monday 14:00 monday 14:00 tuesday 4:00 tuesday 14:00 tuesday 1:00 there will be 7 fields...one for each day and time...so in the abov example they should display:(i left a lot of fields out below...onl mentioned the ones that are used in the example) saturday/14:00 - 4 saturday/11:00...

Imbedded If Statement maybe If And....
Good Morning, I have a file with over 6,900 rows that originated out of Fox Pro. Th issue is that the unique identifier (Store #) is in a row above th data and the part numbers are in row(s) below the Store #. The amoun of parts differs for each store. Example Row 2- Store 17 Row 3 and 4 contain the parts Store 17 used. Row 5- Store 6001 Row 6,7, & 8 contain the parts Store 6001 used. Basically I want to add the store number to a column that correspond with the part numbers used. In inserted 2 columns (A&B) in A1 there i the text �Store #� In B3 I have a simple if statement =IF...

Using Rank with ties and spliting the differance
Good Morning, The data I have looks like this Bob 193 Scott 193 Paul 204 Ringo 149 JohnL 148 George 172 Pete 148 Rodger 148 Keith 168 JohnE 118 Using RANK(B1,$B$1:B$10,1) I can get the ranks but when there is a tie I want to get the average of the 2 values of the tie. So if the 2nd place is a tie with the 3rd place then (9+8)/2 = 8.5. It also has to allow for a three (or more) way tie for example the 7th, 8th and 9th is a tie so (2+3+4)/3 = 3 result What I want Bob 8 *8.5* Scott 8 ...

Combining IF statement with VLOOKUP and two table arrays
I have a vlookup that returns values from list 1. What I want to do is to combine that with an IF statement, such that if the lookup criteria is NOT present in list 1, (ie the VLOOKUP on its own returns #N/A), then a vlookup is performed on list 2. I've tried using a formula in the format of IF((vlookup1="#N/A),VLOOKUP2) but that doesn't seem to work. The formula doesn't seem to recognise "#N/A" . How can I do this? Thanks Gordon wrote: > I have a vlookup that returns values from list 1. What I want to do is to > combine that with an IF statement, such that...

Handle info using NtQuerySystemInformation: How to differentiate files from folders
Hi, I am enumerating open handles of currently running processes in the system by calling NtQuerySystemInformation from user mode. I get a series of SYSTEM_HANDLE_ENTRY structures (defined in winternl.h) if the call succeeds. To get additional information about these handles, I have to pass the SYSTEM_HANDLE_ENTRY::ObjectPointer to a kernel mode driver. The additional information I'm interested in is the complete file path of file handles. Is there a way to identify whether the SYSTEM_HANDLE_ENTRY::ObjectPointer points to a file or a folder or network device, etc. before passing t...

Can we use the datediff function and put the answer in a cell?
what we want to do is return the difference of this function into a cell in a table, can we do it? On Sat, 12 Dec 2009 11:34:01 -0800, timmone <timmone@discussions.microsoft.com> wrote: Yes, but you shouldn't. Because that would violate an important relational database design principle that says "no calculated fields in the database". Rather you would calculate the value on the fly in a query: select DateDiff("d", myStartDate, myEndDate) as DaysBetween from myTable -Tom. Microsoft Access MVP >what we want to do is return the difference o...

Using IF & AND command in conjunction
I have a sales incentive grid with tenure of loan on one axis & loan size on another axis. For e.g., for a loan size between $6000 to $9999 at a tenor of 24 months, teh incentive payable is $24 whereas for a loan size between $10000 to &19999 at a tenor of 36 months, the incentive payable is $65. There are a total of 7 loan bands on one axis and 5 tenor bands on the other axis. How do i create a formula so that excel picks the correct incentive amount depending upon the loan amount and tenor combination. Its so confusing. Pls help.. =INDEX(B2:M20,MATCH(tenure,A2:A20,0),MATCH(l...

IF statement with FALSE value being text AND cell reference? #2
Ok, I found the answer to my own questions, but now it presents a ne question. The formula I want works by adding "&" so it reads: =IF(D9>1,(D9*D8),"<"&(D9*D10)) Now my problem is, the resulting number of D9*10 has like 8 decima places. The cell is ignoring my command to round off to just 2 decima places, I guess since it is part of an IF command, and not just general number in the cell. Or maybe because it has text placed i front of it? Not sure why, but how do I make this FALSE statemen return <#.## and not <#.######## ? -- jcob -------------...

Testing an if statement and placing result in different cell
Does anyone know if there is a way of checking if something is true and if so placing a number in another cell - Something like: =if(x>=y,c3=a1+a2,0) ??? Don't know if I would use the if statement here or not - any help much appreciated...:D No - worksheet functions can't change values in other cells. However, in C3, you could put: =IF(x>=y, A1+A2, 0) In article <RuudsRightFoot.swxxz@excelforum.com>, RuudsRightFoot <RuudsRightFoot.swxxz@excelforum.com> wrote: > Does anyone know if there is a way of checking if something is true and > if so placing a nu...

adding figures that match 2 true and 1 false if statement conditio
As ever many thanks in advance for any help. I am struggling to understand how I can say not equal in an if statement in excel:( I need to consildate a table which layout is basically: Col A Col b Col c Col d Col e (name Car basic Bonus status) sid 50 100 10 On hold mary 20 50 5 approved sid 40 90 10 rejected sid 30 35 10 review As this is to do with a budget the consolidation will use the same headings but the status I want to exclude from...

if then else statement also using "and"
I am trying to use the following but it isn't working properly. Can someone please advise what I am doing wrong? If Sheets("E").Range("C8").Value = "1" And Sheets("E").Range("C2").Value = "Yes" Then Sheets("1").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ElseIf Sheets("E").Range("C8").Value = "1" And Sheets("E").Range("C2").Value = "No" Then Sheets("1").Select Rows("24:27").Sel...

INSERT OBJECT USING (IF(AND
I would like to use the (if(and function to compare a condition and if it is true I want to insert a red flag in the cell next to it. 1) What is the (if(and formula for inserting an object and 2) any ideas on finding a red flag to insert? Hi Have a look at the graphics in Wingdings fonts. You may find something you like there. As for the formula, post back with what you have and what you want . . . Andy. "RBOWMAN" <anonymous@discussions.microsoft.com> wrote in message news:139c01c3df6e$78cd4160$a601280a@phx.gbl... > I would like to use the (if(and function to compar...

Averaging with and if statement
I'm trying to create an average from a table with multiple records in it that I identified as "originated by". I only want my form to show the averages of certain records (originated by = continuous improvements"). Right now ALL the records are being averaged with the Control Source: =Avg( [NumberofDaystoClose]). I'm trying to add the criteria that only those records identified as "continuous improvement" records should be averaged. I don't know Access very well at all. I assume I need to add some type of If, or When statement to that expression...

IF AND Statement
Can some please explain how to write a statement that will do the following A1 has value of N B1 has value of -100 I am wanting to put a formula in C1 that says if A1=N and B1 is less than 0 put a YES in C1. I cannot figure this out. Please Help! =IF(AND(A1="N",B1<0),"YES","NO or whatever you want") -- Kind Regards, Niek Otten Microsoft MVP - Excel "mike" <mike@discussions.microsoft.com> wrote in message news:86A2A7FC-7ABA-49A8-9403-F3593B3C8446@microsoft.com... > Can some please explain how to write a statement that will do t...

Using countif and isnumber
I am trying to count the number of cells that have a number, instead of a blank or an na(). Here is what I tried, =COUNTIF(B6:B1489,ISNUMBER(B6:B1489)) This doesn't work, anyone have a solution? Thanks, Aaron =COUNT(B6:B1489) :-) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Aaron&q...