Vba question

worshipexcel

New Member
Joined
Mar 10, 2016
Messages
3
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Meggesto

Board Regular
Joined
Mar 8, 2016
Messages
216
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.
 
Last edited:

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top