# Thread: Count and store as variable

1. 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"

2. 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

3. 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?

4. 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```

5. 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

6. 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.

7. 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.

8. 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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•