Help with inserting increasing number in a column

kbriaz

Board Regular
Joined
Dec 5, 2013
Messages
91
Hi,

I need help with below macro. I want to change it so it only inserts number '1' into first 16 rows. next 16 rows i want it to insert number '2' & number '3' for the next 16 rows & so on only where value in Range(D7:D200) is 'uphold' or 'reject'. I want it to continue until there is a value in Range (B7:B200)


Sub Add_box_numbers()


Range("P7:P200").Select
For Each cell In Selection
If cell.Value = "" Then
Else
cell.Value = cell.Value + 1
End If
Next cell


End Sub

Is it possible ? Many thanks for your help in advance.

P.s Basically these are box numbers i am trying to add. ( 16 in one box )
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
Hi,

Not super pretty, but does the job:

Code:
Sub kbriaz20140122()    
    Dim rValRng As Range, c As Range
    Dim x As Integer, y As Integer
    
    Set rValRng = Range("B7:B200")
    y = 1
    x = 1
    For Each c In rValRng
        If c.Value = "" Then
            If x = 16 Then
                y = y + 1
                x = 0
            End If
            If LCase(c.Offset(0, 2).Value) = "uphold" Or LCase(c.Offset(0, 2).Value) = "reject" Then
                    c.Offset(0, 14).Value = y
                    x = x + 1
            End If
        End If
    Next c
    
    Set rValRng = Nothing
    Set c = Nothing
    y = Empty
    x = Empty
End Sub
 

kbriaz

Board Regular
Joined
Dec 5, 2013
Messages
91
Many thanks mate, i shall report back on its performance. your help is much apprecieated
 

stefankemp

Board Regular
Joined
Mar 11, 2010
Messages
136
I need help with below macro. I want to change it so it only inserts number '1' into first 16 rows. next 16 rows i want it to insert number '2' & number '3' for the next 16 rows & so on only where value in Range(D7:D200) is 'uphold' or 'reject'. I want it to continue until there is a value in Range (B7:B200)

Does the "first 16 rows" start from row 7? You can do this much quicker using a formula. Assuming you want the numbers to appear in P7:P200, place the following formula in P7 and then copy down:

=IF(AND(OR(D7="uphold",D7="reject"),COUNTA(B$7:B7)=0),INT((ROW(A1)-1)/16)+1,"")
 
Last edited:

kbriaz

Board Regular
Joined
Dec 5, 2013
Messages
91

ADVERTISEMENT

Guys, thanks & i humbly apologize

Its my fault.

I didn't explain it well enough.

Excel noob here :(

I want starting from P7 filled with number '1' until count=16 i.e P7=1, P8=1, P9=1 until 16 rows.

Once 16 cells are filled with number '1' i want 17th cell to do same as above but with number '2' & so on..

Imagine you have 50 items which are listed in a column.

On other side i want to see which item is in which box.

So, first 16 items will be in box 1

Next 16 items in box 2

Condition which i need to apply is If B7 is not blank & D7 is 'Uphold' or 'Reject'.

Above macro didn't work for me :(

Neither did the formula ( i tried in both cases, upper & lower )

Thanks again
 

stefankemp

Board Regular
Joined
Mar 11, 2010
Messages
136
In that case, change the formula to:

=IF(AND(OR(LOWER(D7)="uphold",LOWER(D7)="reject"),B7<>""),INT((ROW(A1)-1)/16)+1,"")
 

kbriaz

Board Regular
Joined
Dec 5, 2013
Messages
91

ADVERTISEMENT

In that case, change the formula to:

=IF(AND(OR(LOWER(D7)="uphold",LOWER(D7)="reject"),B7<>""),INT((ROW(A1)-1)/16)+1,"")

WOW, amazing work SIR !

I know i am taking the mickey now but I slightly have a different issue

I want a different condition ( as i have few different product types )

If B170 is not blank & D170=Referral I want it to look for highest number in range P7:P156 + 1 & start from this number downwards

I.e if last box number we ended up above was '5' then for Referrals i want the box number to start from 6 rather then number '1'

If it is not possible, then that's fare enough.

P.s As rows will get deleted we can't rely on a particular cell, if it helps its 'P156' which will always change as this row most defiantly will get deleted, I am trying to setup a template.

@ stefankemp @ JamesW Thanks Both
 
Last edited:

kbriaz

Board Regular
Joined
Dec 5, 2013
Messages
91
=IF(AND(LOWER(D7)="referral",B7<>""),INT((ROW(A1)-1)/16)+1+MAX(P7:P156),"")


Thanks for your quick reply

=IF(AND(LOWER(D170)="Referral",B170<>""),INT((ROW(A1)-1)/16)+1+MAX(P7:P156),"")

Above gives me blank.

If you place this in P170 & in P147 just type number 5 you will see what i mean. P170 should say 6 instead i am getting blank !

Ta mate
 

Watch MrExcel Video

Forum statistics

Threads
1,123,489
Messages
5,601,982
Members
414,489
Latest member
Xlambda

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