# Posts by ejlupien

• ## Count By Criteria Of Corresponding Cells

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)

• ## Return Variable From Other Procedure

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.

• ## Return Variable From Other Procedure

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!

• ## Count Specific Day Name Between 2 Dates

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

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

• ## Count Specific Day Name Between 2 Dates

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

Thank you guys ... I got it working! This saves me!!!

• ## Count Specific Day Name Between 2 Dates

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

• ## Display Rows That Have Highest 10 Values In Range A1:a100

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

• ## Checkbox Pops Up Msgbox

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``````
• ## Checkbox Pops Up Msgbox

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.