Vba question

[worshipexcel]

Hi could anyone help me with the code please ? I m trying to get a code to count the number of items in a range using loops I would need it to execute a code as soon as the number of items are greater than or equal to 3
For eg: there is a list of supermarkets such as tesco, sainsbury and marks and Spencer selling different types of fruits and as soon as each supermarket has sold 3 red apples the counter should reset to 0 and do the same for other supermarkets.

Thanks

 

[Meggesto]

I'd need more information to help you. This can be done if that's what you're asking. Where is the data in your spreadsheet? What types of values is it comprised of? What code do you need executed after the counter finds more than 3 values? A little more detail here would be very effective in finding you the proper solution. Even a picture of the data would help greatly.

 

[hiker95]

worshipexcel,

I would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729

 

[L. Howard]

This is not a loop, but may do what you want.

Where the store names are in cells B1to F1...
The fruit are listed in cells A2 to A6...

Copy to the sheet module. Reacts to changes within the range given in the code.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Intersect(Target, Range("$B$2:$F$6")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
  
  Dim frutNme As String, storNme As String
    
  If Target.Value >= 3 Then
     frutNme = Cells(Target.Row, 1)
     storNme = Cells(1, Target.Column)
     MsgBox storNme & " " & frutNme & " is three or greater, reset to ZERO"
     Target.Value = 0
    Else
    
  End If
End Sub