VBA code to restart S/N from 1

[CalKlein]

Hi, I'm new to VBA and would hope to get some help here.
At column D, I have sorted all "B" follow by "P".
I have the undermentioned code to list the S/N for column A from 1 onwards.
But I would need the S/N to start from one again once the Column D value is "P".
Your assistance is very much appreciated.

Seems like I can't upload a picture so appreciate your help with above wordings.
Thanx.

Dim k As Integer
Dim LR As Integer
'Find the last row with data
LR = Range("D" & Rows.Count).End(xlUp).Row

For k = LR To 2 Step -1
If Cells(k, 4).Value = "B" Then
Cells(k, 1).Value = k - 1
ElseIf Cells(k, 4).Value = "P" Then
Cells(k, 1).Value = k - 2
End If
Next k

 

[Andrew Poulsom]

Welcome to MrExcel.

Try:

Sub Test()
    Dim LR As Integer
'   Find the last row with data
    LR = Range("D" & Rows.Count).End(xlUp).Row
    With Range("A2:A" & LR):
        .FormulaR1C1 = "=COUNTIF(R2C4:RC4,RC4)"
        .Value = .Value
    End With
End Sub

 

[L. Howard]

Hi Andrew,

What would the formula code look like to do the equivalent of this?

=D2&" - "&COUNTIF($D$2:$D2,$D2)
.Value = .Value

B - 1
B - 2
p - 1
p - 2

Howard

 

[L. Howard]

Perhaps I should have given this a little more thought before posting...

Sub Test()
    Dim LR As Integer
'   Find the last row with data
    LR = Range("D" & Rows.Count).End(xlUp).Row
    With Range("E2:E" & LR):
        '.FormulaR1C1 = "=COUNTIF(R2C4:RC4,RC4)"
        .Formula = "=D2&"" - ""&COUNTIF($D$2:$D2,$D2)"
        .Value = .Value
    End With
End Sub

Howard

 

[CalKlein]

Welcome to MrExcel.

Try:

Sub Test()
    Dim LR As Integer
'   Find the last row with data
    LR = Range("D" & Rows.Count).End(xlUp).Row
    With Range("A2:A" & LR):
        .FormulaR1C1 = "=COUNTIF(R2C4:RC4,RC4)"
        .Value = .Value
    End With
End Sub

Thank you very much, Sir! The code works perfectly.