+ Reply to Thread
Results 1 to 5 of 5

nested if then else statement

  1. #1
    Forum Contributor
    Join Date
    10-01-2012
    Location
    england
    MS-Off Ver
    Excel 2016 pro
    Posts
    772

    Post nested if then else statement

    hi
    as previous thread i have a code below
    Please Login or Register  to view this content.

    this copies complete rows if cell q has a value of yes
    question
    i want to only copy the first 6 (a-f)inclusive) cells and cell (q) if cell q is yes and the first 6 cells are populated
    can i add under
    Please Login or Register  to view this content.
    not sure what to put in in place of "*"
    also i want , only if the first 6 cells are populated for it to enter a "yes"in cell q then it would run the first code,
    thought this code was a good reference point to start from,looked simple at first but been messing for 8 hrs now and still not there,ive read up on nested funtions
    im lost any help would be great to get me away again
    cheers colin
    Last edited by cfinch100; 02-23-2013 at 08:21 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: nested if then else statement

    1) Row1 needs to be your titles, data starting in row2
    2)You should consider just putting a formula in Q2 and copying down that will give you a YES when A:F are filled.
    =IF(COUNTA(A2:F2)=6, "Yes", "No")

    3) The following macro will apply an AutoFilter to your sheet using row1 titles. It will filter Q for "Yes", then it will filter A:F to show only the rows that have values in all 6 columns. Then it will copy the data remaining visible.

    4) Optional, we could add where it deletes the rows from the mfr_list after they are copied to the COMPLETE sheet.

    5) Truth be told, since this macro is checking the columns A:F for you, maybe you don't really need the YES thing in Q at all.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    10-01-2012
    Location
    england
    MS-Off Ver
    Excel 2016 pro
    Posts
    772

    Re: nested if then else statement

    hi
    thanks for reply first
    the reason that i wanted to put the yes in "q" was that when it goes through data any row that has a "yes" would not be copied again just left there ,any row that has a to f newly populated would be copied to sheet "completed" and a (yes( placed in (q) so that next time that would not be copied to "completed",
    think i overthinking it and getting tied up in knots
    cheers colin

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: nested if then else statement

    So my macro won't work because it is currently including the YES rows from column Q. So what you really need is to filter column Q for <>Yes, then copy, then insert YES into those copied rows.

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    10-01-2012
    Location
    england
    MS-Off Ver
    Excel 2016 pro
    Posts
    772

    Re: nested if then else statement

    hi jerry
    works brilliantly your a star many thanks for code and help given might be able to carry on for while maybe
    cheers colin
    Last edited by cfinch100; 02-23-2013 at 08:23 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1