Restarting Sequential Numbers

[MHamid]

Hello,

I need to add sequential numbers in column AX based on the change invalues in column BC.
Column BC is already sorted so all like values are together.
The data in column AX starts at cell AX5.

Is it possible to create a vba code that will add sequential numbersbased on the changes in column BC?
The values in columns BC will always be “Up” and “Down”. For instance,
Column AX (row 5) Column BC
1 Up
2 Up
3 Up
1 Down
2 Down
3 Down

Thank you

 

[shg]

How about just a formula?

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]Up[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td="bgcolor:#CCFFCC"]B2: =1 + (A2=A1)*N(B1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]Up[/td][td="bgcolor:#CCFFCC"]

2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]Up[/td][td="bgcolor:#CCFFCC"]

3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]Down[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]Down[/td][td="bgcolor:#CCFFCC"]

2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]Down[/td][td="bgcolor:#CCFFCC"]

3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]Up[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]Up[/td][td="bgcolor:#CCFFCC"]

2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]Down[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td][/td][/tr]
[/table]

 

[MHamid]

I am automating a process and this is one piece that needs to beincluded with the automation.
I do have the following formula in vba code, but it’s not workingproperly. It worked the first time and now it’s not working.
It is adding the formula in cell AX513 and I am not sure why sincethere is no data that far down. The end of the data in row AY is currently 6rows of data (will change monthly). Also the copying and pasting as values

'Add Sequential Numbers
    Dim lastRow1 As Long
    lastRow1 = Cells(Rows.Count,"AY").End(xlUp).Row
 
    Range("AX5" &lastRow1).formula = "=IF(BC6=BC5,AX5+1,1)"
    Range("AX:AX").Copy
   Range("AX:AX").PasteSpecial Paste:=xlPasteFormulas,Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode =False 

This code will only give me a count of the first cell only, but itleaves it at 0. I need this formula copied to the last row of data. Just theformula only not the cell formatting.

'Add Sequential Numbers
   Range("AX5").formula = "=IF(BC6=BC5,AX5+1,1)"
    Range("AX:AX").Copy
   Range("AX:AX").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode =False

What am I doing wrong?

Thank you

 

[MHamid]

Hello,

I just figured it out. My formula was off.

'Add Sequential Numbers
    Range("AX5:AX"& lastRow1).formula = "=IF(BC5=BC4,AX4+1,1)"
    Range("AX:AX").Copy
   Range("AX:AX").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode =False

Thank you