Assign sequential number to the cell with condition

[billmark]

Hi
I have the following 2 columns. I want to assign the order no. for each client using VBA as below. There are several worksheets in the workbook. But the code doesn't work. Please help.
[TABLE="width: 201"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 201"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Order[/TD]
[TD]Client[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]549465[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]549465[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]549465[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]549465[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]549465[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]549465[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]549465[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]549465[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]549465[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]549465[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]549465[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8789465[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]8789465[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]8789465[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

QUOTE
Sub assignnumber()



Dim lr As Long
Dim WS As Worksheet
Dim x As Long




For Each WS In Worksheets
lr = WS.Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To lr
WS.Range("A2") = 1
If WS.Range("B" & x) = WS.Range("B" & x - 1) Then
WS.Range("A" & x).Value = WS.Range("A" & x - 1) + 1

End If
Next x
Next WS


End Sub
UNQUOTE

Regards
Mark

 

[Peter_SSs]

If you wanted to keep most of that code, you would just need to make these two changes

Sub assignnumber()
  Dim lr As Long
  Dim WS As Worksheet
  Dim x As Long
  
  For Each WS In Worksheets
    lr = WS.Cells(Rows.Count, 2).End(xlUp).Row
    For x = 2 To lr
      WS.Range("A" & x) = 1
      If WS.Range("B" & x) = WS.Range("B" & x - 1) Then
        WS.Range("A" & x).Value = WS.Range("A" & x - 1) + 1
      End If
    Next x
  Next WS
End Sub

BTW, ..
1. My signature block below shows you how to post code & keep its layout formatting.
2. Just saying something doesn't work does not give helpers much to go on. Did it error, give right results in wrong place, give wrong results in right place, do nothing etc?

 

[Rick Rothstein]

I believe this macro will also work...

Sub AssignNumbers()
  With Range("B2", Cells(Rows.Count, "B").End(xlUp)).Offset(, -1)
    .Formula = "=IF(B1<>B2,1,COUNTIF(B$1:B2,B2))"
    .Value = .Value
  End With
End Sub

 

[billmark]

Thanks for both Peter and Rick

Hi Rick
How the code to apply to multiple worksheets in a workbook.

 

[Peter_SSs]

How the code to apply to multiple worksheets in a workbook.

Looks like your column B data is sorted so try

Sub assignnumber_v2()
  Dim WS As Worksheet
  
  For Each WS In Worksheets
    With WS.Range("A2:A" & WS.Cells(WS.Rows.Count, 2).End(xlUp).Row)
      .Formula = "=IF(B1=B2,A1+1,1)"
      .Value = .Value
    End With
  Next WS
End Sub

 

[billmark]

Looks like your column B data is sorted so try

Sub assignnumber_v2()
  Dim WS As Worksheet
  
  For Each WS In Worksheets
    With WS.Range("A2:A" & WS.Cells(WS.Rows.Count, 2).End(xlUp).Row)
      .Formula = "=IF(B1=B2,A1+1,1)"
      .Value = .Value
    End With
  Next WS
End Sub

Thanks.

I have question, why this statement used 2 but not 1

WS.Cells(WS.Rows.Count, 2

 

[Peter_SSs]

I have question, why this statement used 2 but not 1

WS.Cells(WS.Rows.Count, 2

2 means the code uses column B to determine how many rows of data you have in the sheet. Using 1 would mean the code used column A to work that out. If column A is empty (waiting to have the order numbers inserted) then the code will not process any rows. If column A is empty, that was one of the reasons that the code you originally posted did not work for you.

 

[billmark]

2 means the code uses column B to determine how many rows of data you have in the sheet. Using 1 would mean the code used column A to work that out. If column A is empty (waiting to have the order numbers inserted) then the code will not process any rows. If column A is empty, that was one of the reasons that the code you originally posted did not work for you.

Thanks

 

[Peter_SSs]

Thanks

You're welcome.