Excel scripting Question

Discussion in 'Scripting & Programming' started by sirdragon, Jun 5, 2005.

  1. sirdragon

    sirdragon Nibble Poster

    Not sure if this the right forum for this question, been away for a while lol, ( if iti isnt please feel free to move it!!

    running a little excel programme at work,

    basically it is a rota tester, checks for the correct number of earlys lates, Insulin trained personnel, Drivers, females on duty etc, this bit I have managed quiet well with CountIF and various long winded IF(OR) formulas.

    It is the next bit that is causing me problems,

    from the above I have the following

    one column with the staff Names (A)
    28 columns one for each day in the 28 day rota.

    and a selection of "E","L","D","N" for Earlys Lates Day Off and NIghts.

    Now i have to set up a weekly sheet with the Names of the staff on Earlys in the top part of the column(first 5 rows) and the Lates in the lower rows(bottom 5 rows) seperated by a <HR> for handover period.

    my question is how do I take the names of all the early staff and put them in the right section without having gaps for all those who are either on a day off or on a late shift.

    a simple IF(OR ) formula will put them in the right column but with seven or eight gaps for the missing staff???

    I have thought of using Auto filter but cannot set it up to do this automatically ( the staff who will be using this programme are very very computerphobic, needs to be idiot proof. ie Press this button to print out the weekly rotas for lower floor. sort of thing. getting the macro to select print area, and print is no problem.):rolleyes:

    any ideas, or is this too long winded an explanation??

    many thanks for your patiences
  2. sirdragon

    sirdragon Nibble Poster

    Ii thinkI have most of it sorted using autofilter.

    basically I set up the column with the names in as a range,filter the sheet, then when it is filtered for criteria I need only the relevant names are shown in the col, I then copy and paste in the weekly section day by day, the beauty is that I can automate it for any number of names to be copied as long as they are in the range.

    the only difficulty is that I will not always have a positive result in one of the criteria, ie, not always a night shift worker available. if i set up the criteria to search for an "N" and there isnt one then it copied the whole range, do not know how to get around this one any ideas people???

    cheers martin.
  3. mrobinson52

    mrobinson52 Security Maven Gold Member

    Well, it has been a while since I worked with macros and If statements, but I seem to recall you can have IF(AND), IF(OR), and IF(NOT). It seems to me that you would need an IF(NOT) statement or an ELSE statement.

    IF(A=Night, copyto B)

    Or something to that effect. Like I said, it has been a while (4 or 5 years). :blink
    Certifications: A+, Network+, MCSA:Security, Security+
  4. sirdragon

    sirdragon Nibble Poster

    I think the IF statements wouldnot get rid of the blanks.....tried those:dry

    mostly the auto filter route is working except for the problem of not having an entry for the filter to work with:

    this is the code that i am using to find "N" from within a macro

    Selection.AutoFilter Field:=3, Criteria1:="N"

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Selection.AutoFilter Field:=3

    is there a way of turning off the code if criteria: "N" ="" and go on to the next bit of code?

    just a thought

    cheers martin

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.