Adding sequence numbers based on multiple values to a cell with vba

[wbstadeli]

Hi!

Im wanting to add a sequence number to a cell that gets populated by a linked cell from a combobox selection. The thing is that I want the sequence number to take in account 3 different text values and add the next number if any of those text appear in the column (1) above my input cell (where the new value is getting written to) of a named Range. Here are my dropdown list options in the combobox: Lathe Op, Mill Op, Mill/Turn Op.

For example: if any cell in Range("Labor_Operations_Per_Piece_Range") in column (1) had Mill Op – 1, and Mill Op – 2 and Mill/Turn – Op 3, and I select “Mill Op” from the dropdown the input cell should be Mill Op – 4. So it would add a hyphen and the next sequence number based on just those mentioned dropdown texts because that same column can have other selections that I don’t want to be included as a sequence. So here is my existing code to create a new row in Range("Labor_Operations_Per_Piece_Range") and the line with “this is where I need the code to add the sequence numbering” is where the above code would go. Let me know if you need any more info… Thank you very much for looking at this!

Dim sh As Worksheet
Set sh = Sheets("Quote Sheet")

sh.Unprotect
sh.Activate
Application.EnableEvents = False

Dim R As Range, rws As Long
Set R = Range("Labor_Operations_Per_Piece_Range")
rws = R.Rows.Count

    With R
        'inserts a new row after last row in named range
        .Rows(.Rows.Count + 1).EntireRow.Insert shift:=xlDown
        'renames the inserted row with a name'
        .Rows(.Rows.Count + 1).Resize(, 11).Name = "newrow"
        With sh.Range("newrow")
            'this is where I need the code to add the sequence numbering
            'inputs the linked cell from the combobox, linked cell location is sh.Range("Labor_Operations_Per_Piece_Range").Rows(1).Columns(1)
            .Columns(1).Value = sh.Range("Labor_Operations_Per_Piece_Range").Rows(1).Columns(1).Value
        End With
    End With
   
    Set R = R.Resize(rws + 1)
    R.Name = "Labor_Operations_Per_Piece_Range"
       
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub

 

[Snakehips]

@wbstadeli Can you just clarify. You want a sequence of anything that includes Mill? A separate sequence for Lathe ? Anything other than a milling or turning op is not sequenced?

 

[wbstadeli]

@wbstadeli Can you just clarify. You want a sequence of anything that includes Mill? A separate sequence for Lathe ? Anything other than a milling or turning op is not sequenced?

A sequence that includes "Mill Op" , "Lathe Op" , or "Mill/Turn Op". Or the last way you worded it would work also i think, anything other than "Mill Op" , "Lathe Op" , or "Mill/Turn Op" is not sequenced.

 

[Snakehips]

So Mill ops and Lathe ops part of same sequence?
Eg. Mill Op – 1, and Mill Op – 2 and Mill/Turn Op - 3, Lathe Op - 4, Mill Op - 5. ???

 

[Snakehips]

Assuming yes to the above then, like...

Sub Testit()
Dim sh As Worksheet
Set sh = Sheets("Quote Sheet")
sh.Unprotect
sh.Activate

Application.EnableEvents = False

Dim Op As String
Dim SNum As String
Dim R As Range, rws As Long
Set R = Range("Labor_Operations_Per_Piece_Range")
rws = R.Rows.Count

'set sequence number to nothing
SNum = ""
'asign the selected op text to variable Op
Op = R.Rows(1).Columns(1).Value
'Test if is a Mill  or Lathe op
    If InStr(Op, "Mill") Or InStr(Op, "Lathe") Then
    ' If yes then count previous Mill And lathe ops
        SNum = " - " & WorksheetFunction.CountIf(R.Columns(1), "=*" & "Mill" & "*") _
        + WorksheetFunction.CountIf(R.Columns(1), "=*" & "Lathe" & "*")
    End If

        
    With R
        'inserts a new row after last row in named range
        .Rows(.Rows.Count + 1).EntireRow.Insert shift:=xlDown
        'renames the inserted row with a name'
        .Rows(.Rows.Count + 1).Resize(, 11).Name = "newrow"
        With sh.Range("newrow")
            'this is where I need the code to add the sequence numbering
            'inputs the linked cell from the combobox, linked cell location is sh.Range("Labor_Operations_Per_Piece_Range").Rows(1).Columns(1)
            .Columns(1).Value = Op & SNum
        End With
    End With
   
    Set R = R.Resize(rws + 1)
    R.Name = "Labor_Operations_Per_Piece_Range"
       
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub

Hope that helps.

 

[wbstadeli]

Assuming yes to the above then, like...

Sub Testit()
Dim sh As Worksheet
Set sh = Sheets("Quote Sheet")
sh.Unprotect
sh.Activate

Application.EnableEvents = False

Dim Op As String
Dim SNum As String
Dim R As Range, rws As Long
Set R = Range("Labor_Operations_Per_Piece_Range")
rws = R.Rows.Count

'set sequence number to nothing
SNum = ""
'asign the selected op text to variable Op
Op = R.Rows(1).Columns(1).Value
'Test if is a Mill  or Lathe op
    If InStr(Op, "Mill") Or InStr(Op, "Lathe") Then
    ' If yes then count previous Mill And lathe ops
        SNum = " - " & WorksheetFunction.CountIf(R.Columns(1), "=*" & "Mill" & "*") _
        + WorksheetFunction.CountIf(R.Columns(1), "=*" & "Lathe" & "*")
    End If

       
    With R
        'inserts a new row after last row in named range
        .Rows(.Rows.Count + 1).EntireRow.Insert shift:=xlDown
        'renames the inserted row with a name'
        .Rows(.Rows.Count + 1).Resize(, 11).Name = "newrow"
        With sh.Range("newrow")
            'this is where I need the code to add the sequence numbering
            'inputs the linked cell from the combobox, linked cell location is sh.Range("Labor_Operations_Per_Piece_Range").Rows(1).Columns(1)
            .Columns(1).Value = Op & SNum
        End With
    End With
  
    Set R = R.Resize(rws + 1)
    R.Name = "Labor_Operations_Per_Piece_Range"
      
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub

Hope that helps.

Brillant! Thank you very much!

 

[Snakehips]

You are most welcome.