Posts by ejlupien

    What is wrong with this formula?


    =COUNT(AND(I164="Monday",L164=1)+AND(I165="Monday",L165=1)+AND(I166="Monday",L166=1)+AND(I167="Monday",L167=1)+AND(I168="Monday",L168=1)+AND(I169="Monday",L169=1)+AND(I170="Monday",L170=1)+AND(I171="Monday",L171=1)+AND(I172="Monday",L172=1)+AND(I173="Monday",L173=1)+AND(I174="Monday",L174=1)+AND(I175="Monday",L175=1))


    I am trying to add the number of times two variables in a row are true, in at least 12 rows.


    Thanks.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Just answered my own question.


    =SUMIF(I164:I178,"Monday",L164:L178)

    Re: Return Variable From Other Procedure


    The code is about 13,000 lines long.


    As I said, I have 2 procedures. I call the second procedure from the first, and perform a series of calculations in the second to assign values to a variable.


    I simply need to be able to get the first procedure to know what the value of that variable is once calculated in the second procedure.


    The second procedure follows the first on the same worksheet. I set up both procedures as Private.

    This may seem basic. I've search here for a bit and could not find the answer to what I'm looking for.


    I have 2 procedures on the same worksheet.


    I need to get the value of a variable from a second procedure so that my first procedure can work with it.



    From the first procedure I call the second, where the value of the variable is calculated, but when I return to the first procedure, the value of the variable seems to get lost.


    Does this make sense?


    Help!

    Re: Count Mondays (or Any Other Day Of Week) Between 2 Dates


    Hi daddylonglegs,


    I like any solution to be honest, I wasn't going to be picky *smile*.


    You're right, when I "confirmed the formula" with CTRL+SHIRT+ENTER I got the answer.


    Why did I not get the answer when I just entered the formula in the cell and hit enter? Why would is confirming the formula necessary? When I confirmed the formula, Excel added {} before and after the formula.


    Erik

    I found these 2 examples online on how to count Mondays between 2 dates but neither seems to work on Excel 2003, which I am using. Can anyone help me please?



    EXAMPLE 1


    Number Of Mondays In Period


    If you need to return the number of Mondays (or any other day) that occur within an interval between two dates, use the following Array Formula:


    =SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))


    This formula assumes the following:
    A2 contains the beginning date of the interval
    B2 contains the ending date of the interval
    C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)




    EXAMPLE 2


    Copy the code below to a tab sheet module, the name must be unchanged.
    Then on your worksheet have a cell for the "Start date," another for the "End date" and a cell for the days of the week to be counted "WkDays" as a data cell.


    In the cell you want your day count to be listed put:
    =WkDays(StartDate, EndDate, WkDays)
    Like,
    =WkDays(B3,B4,B5)
    The key to WkDays is: 1=Monday...7=Sunday.


    To count Saturdays and Sundays, WkDays would be 67, to count Saterday WkDays would be 6, to count Monday through Friday WkDays would be 12345 as day data.






    Thank you.


    Erik

    Hi everyone,


    I have a table on a worksheet with numerical values throughout Range A1:L100.


    I need to hide all rows 1 to 100 except the 5 rows that have the top 10 numerical values in Range column A.


    For example, if cells A3, A5, A50, A64, and A75 have the highest nmbered values in Range A1:A100, then rows 3, 5, 50, 64, and 75 would be displayed while all other rows from 1 to 100 would be hidden.


    I know this is doable, I'm just not that good with looping yet!


    If someone could help I would be greatly appreciative.


    Thank you.


    Erik

    Re: Checkbox Pops Up Msgbox


    Thanks for the reply ... not sure why I was thinking about If Not Intersect ... in the end the following does what I wanted.


    Code
    Private Sub CheckBox79_Click()
        If Range("A1").Value = "True" Then
            Range("A2") = "False"
        End If
    End Sub

    Does anyone know how to use a form checkbox that adds the true/false values to a cell to activate a msgbox when the cell value changes?


    I've tried assigning a macro to the checkbox and uses "If not intersect" in VBA but it doesn't work.


    What I'm trying to do is assign a value to cell A1 depending on whether the checkbox assigned to cell A2 is clicked and the value of cell A2 becomes true or false.


    Thank you to anyone who can help me out.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]One more thing to be clearer about my question ... I'm trying to do change the value of cell A1 on click of the checkbox assigned to cell A2, meaning when the value of cell A2 changes.