Posts by zamael

  • [SOLVED] Trying to use an & in a formula through VBA - Error

    I am attempting to use an & in a formula through VBA but I keep getting the 1004 Application-defined error. I recorded this line to get the syntax but I can't get it to work! Any help would be much appreciated.


    Recorded code:

    Code
    ActiveCell.FormulaR1C1 = _        "=IF(RC[-4]=""LOGON"",IF(RC[-3]<strLogon1,""YES"",IF(RC[-2]>strLogon2,""YES"","""")),"""")&IF(RC[-4]=""LUNCH"",IF(RC[-1]>strLunch,""YES"",""""),"""")&IF(RC[-4]=""BREAK"",IF(RC[-1]>strBreak,""YES"",""""),"""")&IF(RC[-4]=""LOFF"",IF(RC[-1]>1,""YES"",""""),"""")&IF(RC[-4]=""CPE"",IF(RC[-2]>strCPE,""YES"",""""),"""")&IF(RC[-4]=""MTNG"",IF(RC[-2]>strMeeting,""YES"","""")," & _
        "(RC[-4]=""FUEL"",IF(RC[-1]>strFuel,""YES"",""""),"""")&IF(RC[-4]=""TRNG"",""YES"","""")&IF(RC[-4]=""AVAIL"",IF(RC[-1]>strAvail,""YES"",""""),"""")&IF(RC[-4]=""ASSIST"",""YES"","""")&IF(RC[-4]=""VEHICLE"",""YES"","""")&IF(RC[-4]=""ONHOLD"",IF(RC[-1]>strHold,""YES"",""""),"""")"



    I tried to make edits to the code to get it to work, this one also gets the same error.


    Code
    Range("G2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-4]=""LOGON"",IF(RC[-3]<""strLogon1"",""YES"",IF(RC[-2]>""strLogon2"",""YES"","""")),"""")" & Chr(38) & "IF(RC[-4]=""LUNCH"",IF(RC[-1]>""strLunch"",""YES"",""""),"""")" & Chr(38) & "IF(RC[-4]=""BREAK"",IF(RC[-1]>""strBreak"",""YES"",""""),"""")" & Chr(38) & "IF(RC[-4]=""LOFF"",IF(RC[-1]>1,""YES"",""""),"""")" & Chr(38) & "IF(RC[-4]=""CPE"",IF(RC[-2]>""strCPE"",""YES"",""""),"""")" & Chr(38) & "IF(RC[-4]=""MTNG"",IF(RC[-2]>""strMeeting"",""YES"",""""),(RC[-4]=""FUEL"",IF(RC[-1]>strFuel,""YES"",""""),"""")" & _
        Chr(38) & "IF(RC[-4]=""TRNG"",""YES"","""")" & Chr(38) & "IF(RC[-4]=""AVAIL"",IF(RC[-1]>""strAvail"",""YES"",""""),"""")" & Chr(38) & "IF(RC[-4]=""ASSIST"",""YES"","""")" & Chr(38) & "IF(RC[-4]=""VEHICLE"",""YES"","""")" & Chr(38) & "IF(RC[-4]=""ONHOLD"",IF(RC[-1]>""strHold"",""YES"",""""),"""")"
    Range("G3").Select

  • Color range of cells (specific number of them) as determined by different cell value

    Re: Color range of cells (specific number of them) as determined by different cell va


    That works... however, not exactly what I needed. So your script changes the date heading to a different color. What I need is the grid below each date to reflect entries in and out of compliance. So get the limit from the limit_set sheet, then color all cells as pictured.


    [ATTACH=CONFIG]67823[/ATTACH]

  • Color range of cells (specific number of them) as determined by different cell value

    Re: Color range of cells (specific number of them) as determined by different cell va


    Quote from Max1616;762298

    Could you post an example workbook?


    See attached. "Calendar" is the selections made (this will eventually be much more full of data) and the allotment for each date is on "Limit_Set"... so if there are more entries on the given date than what the allotment allows, then fill the shades red for those entries over the limit and make the entries that are within limit, fill with green.

  • Color range of cells (specific number of them) as determined by different cell value

    I have 2 sheets of data, one has every day of the year and people that selected that date for vacations. I have another sheet that determines what the limitations are for each day of year.


    I need to highlight the dates on sheet 1 that have more people selected than allowed - those limits are found on a different sheet. Hopefully that makes sense.


    I think I have to do a nested loop but I have no idea where to start. Thanks for the help!

  • Vacation selection process by seniority

    Re: Vacation selection process by seniority


    Quote from S O;762155

    Have the seniority data sorted with most senior at the top, use a MATCH() function to find the employee and whichever function returns the lowest number is the winner.


    Great idea! Sometimes I need to take a step back and think logically...

  • Vacation selection process by seniority

    Been coming here a long time for answers and code snippets; now I'm faced with a problem I can't seem to answer.


    I was given a project at work to assist in the vacation selection process for all employees... I love new projects and tackling things like this but I can't quite wrap my head around how to get it done.


    We have had every employee fill out a vacation selection form. Each employee will have varying dates they have selected for 2016 and some will have none at all. Given our business type, only a certain amount of slots for vacations will be open on any given day and that amount can also vary.


    We also have seniority data ...


    This project will be utilized in other offices so I'm trying to streamline it and make it as "seamless" as possible. What I was thinking was a 5 sheet workbook like this:


    • Employees selections
    • Daily Time Off Limits (so each office can change that amount)
    • Seniority Data -- this is a direct export from our HR system so didn't want to mess with this data, just used for reference
    • Calendar (for a graphical representation of the year, each day with who requested each day regardless allotment)
    • Results tab with specifically who "won" the vacation bid for each day.



    Anyone have any good ideas for how I can set this up or even if you think I'm headed in the 'best' direction?