How to insert rows dynamically with in group Solved/Closed

Question

Hi, 
currently I am creating a macro, in to that I have data for row and columns. now I want to count the product in column A, with formula =counta (A: A).after taken product count, I want divide the product in to 4 group (like example below or sheet 1, 2 and 3 in attach sheet) for that I follow the formula =if (product count>30; product count/4; product count/3) (like shown in example below).after that I want to insert 3 rows after each group dynamically (see Sample data).

For better understanding I have attached sample date file as a Zip format.
In that file on sheet 2 and sheet 3 you will find the data which I have separate with in group based on total no of product. 
ex : if total product is 25 then :
G1 =9
G2= 8
G3 =8
G4 =NA

ex : if total product is 28 then :
G1 =10
G2= 9
G3 =9
G4 =NA

ex : if total product is 30 then :
G1 =10
G2= 10
G3 =10
G4 =NA


ex : if total product is 33 then :
G1 =9
G2= 8
G3 =8
G4 =8

one more ex: if total product is 34 then:
G1 =9
G2= 9
G3 =8
G4 =8


one more ex : if total product is 35 then :
G1 =9
G2= 9
G3 =9
G4 =8

one more ex : if total product is 36 then :
G1 =9
G2= 9
G3 =9
G4 =9

one more ex : if total product is 37 then :
G1 =10
G2= 9
G3 =9
G4 =9

it will apply the same method for "n" of product. 

Then it should sum within group (see sheet 1, 2 or 3 data), it should also show the no of product in each group (like in sample data).

I think it will help for better understanding,

How it can be done? Plz help me......

Thanks you very much for your support,

Virendra

rizvisa1 · Answer

see if this helps  

Sub doDataGroup() 
    
   Dim lCount           As Long       'total items to be grouped 
   Dim iMaxSheetItem    As Integer    'max items on one sheet 
   Dim iSheetNeed       As Integer    'calculated number of sheets needed 
   Dim iCounter         As Integer    'counter to loop thru sheets 
   Dim iSheetItem       As Integer    'number of items of the sheet being processed 
   Dim iRemainder       As Integer 
    
   iMaxSheetItem = 10 
    
   lCount = 37 'example that there are 37 total items 
    
   iRemainder = lCount Mod iMaxSheetItem 
   iSheetNeed = ((lCount - iRemainder) / iMaxSheetItem) + IIf(iRemainder > 0, 1, 0) 

    
   For iCounter = iSheetNeed To 1 Step -1 
      iRemainder = lCount Mod iCounter 
      iSheetItem = ((lCount - iRemainder) / iCounter) + IIf(iRemainder  > 0, 1, 0) 
      lCount = lCount - iSheetItem 
       
      Debug.Print "Item on Sheet " & iCounter & ": " & iSheetItem 
       
   Next iCounter 
End Sub

How to insert rows dynamically with in group

1 response

Similar discussions

Subscribe To Our Newsletter!