Please pardon my English. I try to explain my needs in excel.

I am trying to write an excel VBA code for total number counts in a particular column. for example A:A columns are filled with specified numbers. 150,200,150,175,150,150.... with B column I have a code that counts how many 150's and 200's with a code of "COUNTIFS". its an easy code. (=COUNTIFS(A:A,B2))

1. Now I need every fifty numbers of 150's I should get number 1 in a particular cell ( for example, C2) then another fifty numbers of 150 (that means hundred 150's) I should get number 2 in a same C2 cell. this will continue..

2. A reset button needed to reset this C2 value, again it should count every fifty number of 150's after reset start from beginning.

A data entry in a A column is a particular Sweet box number to particular shop. once I get a fifty sweet boxes delivered as per data, I should count 1. then another fifty sweet boxes it should count 2. once I demand a payment, I should reset the counts (not entire counts.). then again it start counting from one.

I think I explain my needs, and I hope you all are understand.

please help with VBA code.

CodePudding user response：

Here is the code:

Write this code in the new module and call the function in the excel cell (Ex: C2).

Public Function COUNTCUSTOM(counted As Integer, rupee As Integer)
  'counted -  Total number of 150's
  'rupee - 50  if 150 count reach 50 i will be 1, if 150 reached 100 i will 2.
 Dim i As Long
 i = counted \ rupee
 COUNTCUSTOM = i

End Function

CodePudding user response：

Here is the simplified version of my question

1. When I click the reset button Cell C2 should be clear
2. After reset no changes in total box delivery. but a fresh start to count another set of delivery

I have changed 50 number a set to 5 a set for your convenience

1. Total button's code as below

Sheets("sheet1").Range("B2").Formula = "=COUNTIFS(A:A,D2)"