VBA Message box - count instances in column to return result

[mrsushi]

Good afternoon,

Trying to incorporate other columns into the formula to count the number of instances which flag up the narrative "Check". So far the below formula counts up the value in column H, but I need a count for column I, L and M.

When the message box appears how can I for each line indicate the number of instances in each column?

Many thanks
M

Sub CheckCounterMessageBox()
   
Dim instances As Long
   instances = WorksheetFunction.CountIf(Columns("H"), "CHECK")
   MsgBox "Found " & instances & " Price(s) to correct on DFL Ladder BID", vbInformation, "LS73 Reconcilations"
  
End Sub

 

[JoeMo]

Try:

Sub CheckCounterMessageBox()
Dim instances As Long, Cols As Variant
Cols = Array(Columns("H"), Columns("I"), Columns("L"), Columns("M"))
For Each col In Cols
   instances = WorksheetFunction.CountIf(col, "CHECK")
   MsgBox "Found " & " in Column " & Left(col.Address(0, 0), 1) & ":  " & instances & " Price(s) to correct on DFL Ladder BID", vbInformation, "LS73 Reconcilations"
Next col
  
End Sub

 

[dmt32]

Hi,
one way maybe

Sub CheckCounterMessageBox()
    Dim instances As Long
    Dim rng As Range, col As Range
    
    Set rng = Range("H:H,I:I,L:L,M:M")
    For Each col In rng.Columns
        instances = instances + WorksheetFunction.CountIf(col, "CHECK")
    Next
    
    MsgBox "Found " & instances & " Price(s) to correct on DFL Ladder BID", vbInformation, "LS73 Reconcilations"

End Sub

but sure someone will have a non-looping suggestion

Dave

 

[mrsushi]

Many thanks

Try:

Sub CheckCounterMessageBox()
Dim instances As Long, Cols As Variant
Cols = Array(Columns("H"), Columns("I"), Columns("L"), Columns("M"))
For Each col In Cols
   instances = WorksheetFunction.CountIf(col, "CHECK")
   MsgBox "Found " & " in Column " & Left(col.Address(0, 0), 1) & ":  " & instances & " Price(s) to correct on DFL Ladder BID", vbInformation, "LS73 Reconcilations"
Next col
 
End Sub

many thanks for the formula. This sums up the number of checks which is good. However, is it possible to group the count as per column? I've attached image so you can see how many checks per column.

 

[mrsushi]

Hi,
one way maybe

Sub CheckCounterMessageBox()
    Dim instances As Long
    Dim rng As Range, col As Range
   
    Set rng = Range("H:H,I:I,L:L,M:M")
    For Each col In rng.Columns
        instances = instances + WorksheetFunction.CountIf(col, "CHECK")
    Next
   
    MsgBox "Found " & instances & " Price(s) to correct on DFL Ladder BID", vbInformation, "LS73 Reconcilations"

End Sub

but sure someone will have a non-looping suggestion

Dave

many thanks for the formula. This sums up the number of checks which is good. However, is it possible to group the count as per column? I've attached image so you can see how many checks per column.

 

[JoeMo]

Many thanks

many thanks for the formula. This sums up the number of checks which is good. However, is it possible to group the count as per column? I've attached image so you can see how many checks per column.

Look again at my post which I edited after you read it. It now gives you the count by column in separate message boxes.

 

[mrsushi]

Look again at my post which I edited after you read it. It now gives you the count by column in separate message boxes.

Thanks Joe....I see it now. much appreciated

 

[dmt32]

many thanks for the formula. This sums up the number of checks which is good. However, is it possible to group the count as per column? I've attached image so you can see how many checks per column.

maybe

Sub CheckCounterMessageBox()
    Dim instances As Long
    Dim rng As Range, col As Range
    Dim str As String
    
    Set rng = Range("H:H,I:I,L:L,M:M")
    For Each col In rng.Columns
        instances = WorksheetFunction.CountIf(col, "CHECK")
        str = str & Left(col.Address(0, 0), 1) & " - " & instances & Chr(10)
        
    Next
    
    MsgBox "Found Price(s) to correct on DFL Ladder BID" & Chr(10) & str, vbInformation, "LS73 Reconcilations"

End Sub

Dave

 

[JoeMo]

Thanks Joe....I see it now. much appreciated

You are welcome - thanks for the reply.