Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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

  3. #3
    Bronze Lounger
    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?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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

  5. #5
    Bronze Lounger
    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
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  7. #7
    Bronze Lounger
    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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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

  9. #9

Posting Permissions

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