Results 1 to 9 of 9
Thread: Count and store as variable

20090705, 08:22 #1
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
I need to count how many times "AVI" is listed in the active sheet, column F, also where column M is blank, and store this as a variable integar "Cnt".
Then:
I want to add a MsgBox advising the user how long a process is estimated to take. If each "Cnt" takes 20 seconds and there are 45, then >
Msgbox "This process will take approximately xx 15 xx minutes"
How please?

20090705, 09:08 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
I'd put a formula in a cell
=SUMPRODUCT((F1:F1000="AVI")*(M1:M1000=""))
It can also be placed in a cell on another sheet (which can be hidden); you then need to include the sheet name in the formula:
=SUMPRODUCT(('Data Sheet'!F1:F1000="AVI")*('Data Sheet'!M1:M1000=""))
You can refer to the value of the cell with the formula in your code:
Dim lngCnt As Long
Dim lngMinutes As Long
lngCnt = Worksheets("Other Sheet").Range("A1")
lngMinutes = lngCnt * 20 / 60
MsgBox "This process will take approximately " & lngMinutes & " minutes", vbInformation

20090705, 09:25 #3
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thanks Hans, I can do that, but...
My w/s contains a vast amount of formula's that only need to be refreshed at certain points, so we keep calculation mode off. I know I can refresh but that will take time and slow down the code. Is it possible to do this without formula?

20090705, 09:32 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
You could calculate the count in a loop:
Code:Dim lngCnt As Long Dim r As Long lngCnt = 0 ' Adjust the bounds as needed For r = 1 To 1000 ' Substitute the correct name for the sheet If Worksheets("Data Sheet").Range("F" & r) = "AVI" And _ Worksheets("Data Sheet").Range("M" & r) = "" Then lngCnt = lngCnt + 1 End If Next r

20090705, 10:15 #5
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thanks Hans, I'm nearly there!
My ending MsgBox is reporting that 1 item is found, but it is not. Where am I going wrong?
[attachment=84559:AVI_LookUp.xls]
Thanks

20090705, 10:24 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
When I click the button, I get a message that there are 11 AVI accounts to be looked up, not 1. This is correct according to the criteria that you specified: column F contains "AVI" and column M is blank.
BTW, the AccountChain macro doesn't belong in the ThisWorkbook module, it should be in a standard module. You should use ThisWorkbook exclusively for workbook event procedures.

20090705, 10:28 #7
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Hans,
Yes, the opening MsgBox works as expected, but the closing MsgBox says that 1 is found, which it is not
BTW........ > Thanks, I will move it.

20090705, 10:40 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
The line
lngCnt2 = 0
resets lngCnt2 to 0 each time. You should move this line out of the loop, before the line
For RowNum = 2 To HighRow Step 1

20090705, 10:51 #9
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts