Posts by Batman

    Hi Bob,

    Do the cells you are copying from in the statement:

    Range("A" & i & "D:" & i).Copy t.Cells(k, 1)

    have any fill colour formatting, e.g. white instead of 'no fill'? If so, try using Copy & Paste Special / Values.

    Hi Excelpower,

    The reason you are seeing False in cell P2 is that the formulae in cells P3:P40 should be:

    =Sum(B3:O3)=105 (filled down)

    This formula should test whether the sum of the row = 105 and should give a value of True or False, not simply the sum of the row. The array formula in cell P2, which you have entered correctly, is checking whether all the values in cells P3:P40 are =True.

    You can either change the formulae in the individual rows of column P as suggested, or change the array formula to:
    =(P3:P40=105) (again entered with Shift/Ctrl/Enter to give an array formula)

    The first step of the program is checking whether the value in Cell P2 =True; if not it is exiting the program without doing anything. This is why you have no values in the Rota worksheet.

    If you are using an example of 38 employees, which I did in my first reply to the thread, please note the latest version of the program which contains fixes to a few earlier errors. You may also need to use 70 or more employees to test out that the program allocates all 140 available shifts.

    Hope you find this of use.


    Holiday Shift Bid

    Hi Rod,

    Just spotted a typo in the declarations (Dim rngPreferences ...) which may be affecting your program. Anyhow, the full (hopefully correct) version is below:

    You will need to use the latest version, as the first one produced the wrong results (1,3 instead of 1,2) and also errored when it had checked all 14 preferences for an employee and hadn't been able to allocate him(/her) to 2 shifts.

    I hope this works for you, but let me know if you still have problems.


    Hi Nabeel,

    I'm not sure how filling empty cells with 0 or - values will speed up either saving or filtering your data.

    If any of the values in your list are created using formulas, it may speed up filtering if you turn off automatic calculation before filtering.

    Hope this helps.


    Exit event

    Hi kbass and welcome to the forum,

    The frame's _Exit event is activated in the circumstances you describe.

    You haven't given any details of other controls inside the frame; is it possible to use this event to process the formatting changes you want?


    Holiday shift bids

    Hi Rod,

    A few basic mistakes - the biggest of which was to develop and test this very late on a Friday afternoon!

    I have increased the number of employees to 200 (but this can be any number, provided that there are no empty rows) and block copied the preferences and formulas to fill the new range.

    1) Add a new declaration for a number of employees:
    Dim intLastEmployee as Integer

    2) Find the last employee record:
    intLastEmployee = Worksheets("Preferences").Range("A3"). _
    End(xlDown).Row 'A3 is my first employee record

    3) Change the set commands to reflect the variable number of records:
    Set rngPreferences = Worksheets("Preferences"). _
    Range("A3:P" & intLastEmployee)
    Set rngRota = Worksheets("Rota").Range("A3:P" & intLastEmployee)

    4) Change the For/Next loop for the variable number of employees:
    For emp = 1 To (intLastEmployee - 2) ' assumes 2 header rows

    5) Change the first Do command to:
    Do Until intAlloc = 2 or intPref > 14
    This is necessary as you may find that towards the end of the rota allocation, only one of your employees' selections is available (e.g. if you use up all 10 shifts for numbers 1-13 leaving only 14 to allocate).

    6) Delete the first of the 2 commands (within the If/End If block):
    intPref = intPref + 1
    This was what was causing the preferences to be allocated 1,3 instead of 1,2 etc.

    This now seems to work for me, but let me know if you have any questions.

    I have taken a few shortcuts in hard coding the number of preferences, shifts available to each person and people on each shift, so if you want to re-use this regularly there are a number of tweaks to make these numbers variable.


    Hi rbjexcpa,

    Check out the code below. I have created a workbook with 2 worksheets - Preferences for the employee selections and Rota for the results.

    Each sheet has 38 rows for employees (column A, rows 3:40) and 14 columns(B:O) for shifts. Column P has formulae to check that each employee has selected the numbers 1-14 (in any order)
    =SUM(B3:O3)=105 (in P3, filled down)

    and cell P2 has an array formula (entered with Shift/Ctrl/Enter) to check that all of the employees check out OK

    The result should be that each employee's top 2 available preferences are entered into the Rota worksheet.

    Hope this helps & let me know if you need any explanation of the logic.


    Hi orbsplateau,

    You say you want to copy the data "to another sheet"; the problem is whereabouts in that other sheet?

    I believe you will only be able to use Activecell in the destination part of the "copy destination" command if the "other sheet" is the active sheet. Once we have determined how to identify the location of the cell you want to paste the data into, we will be able to work out how to structure the command.

    I suspect that, unless you are copying to within the same worksheet, you will not be able to use Activecell in both halves of a single command.


    Following on from Will's discovery of certain cells being headers, I've been experimenting with creating a named range of non-consecutive cells (e.g. A2:A4, A7:A12, etc) and using this in a formula.

    However, while I can use Sum and Count on this range, I can't use SumIf or CountIf (at least not in XL97), so at the moment I can't see a better way than Will's.

    Hi orbsplateau,

    Sorry for the delay, my last post was just before going home.

    What concerns me is, even if it were possible to select the active cell of another sheet for copying to, how reliable this would be in terms of being the correct destination. Is it not possible to determine the position of the start cell to copy to by some other means (e.g. a specific cell address such as A1, or the next empty cell under a specific heading, etc.)? This would allow you to calculate the cell to copy to before the Copy command and you would then be able to use the Destination in the same statement.