Help with inserting increasing number in a column

[kbriaz]

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 )

 

[kbriaz]

B170 will contain text & numbers combines I.e Test01 or Test00000010

D170 will only contain text i.e Referral

Or, it could count where in column 'D' it says 'Rejection' & across 12 cells i.e Column 'P' it looks for Max number.

Then adds +1 to Max number found, which will be the first number for 'Referrals'

I.e Rejections ended up in box 5, then Referrals will end up in box after which is 6.

Sorry to complicate things even further

 

[stefankemp]

Does B170 and D170 CURRENTLY contain values? If not, then my formula will give a blank result. If they do, and D170 = "Referral", then my formula should work. If that is not the case then you will have to send me the workbook so I can examine it.

 

[kbriaz]

Does B170 and D170 CURRENTLY contain values? If not, then my formula will give a blank result. If they do, and D170 = "Referral", then my formula should work. If that is not the case then you will have to send me the workbook so I can examine it.

Yes they do.

Result i get is 0 for below

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

I will attach spreadsheet soon - ta

 

[exceldevs]

"Referral" should be "referral"

 

[kbriaz]

"Referral" should be "referral"

Tried it both ways mate furitless

 

[kbriaz]

I am great full guys, thanks for the help. Ta