# How to auto populate a cell with information based on criteria



## Hitman51 (Dec 8, 2022)

I need trying create a vba code to create a unique number in each row in column A that I am working on to auto populate when I activate content completed with a userform.  I am looking for something as follows:  In a userform, when OK is pressed, it will automatically create this unique number in the next available cell in column A, with the format being YY (current year) - ### (number starting with 001).  YY-###   Example:  A1 will be 22-001, where the next number auto populated would be 22-002, etc.  Then when 2023 comes around, the sequence should restart at 23-001, 23-002. Etc. 

I hope this makes sense and appreciate anybody's help.


----------



## kevin9999 (Dec 9, 2022)

Try the following.  It assumes you won't have more than 999 new numbers each year.  Change "Sheet1" to your actual sheet name.

```
Private Sub CommandButton1_Click()
    Dim ws As Worksheet, LRow As Long, yr As String
    Set ws = Worksheets("Sheet1")                   '<~~~ change to actual sheet name!
    LRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    yr = Format(Date, "yy") & "-"
    
    'Test if first entry (assumes that row 1 contains headers)
    If LRow = 1 Then
        ws.Cells(LRow + 1, 1) = yr & "001"
        Exit Sub
    End If
    
    'If this is not the first entry...
    If Left(ws.Cells(LRow, 1), 3) <> yr Then
        ws.Cells(LRow + 1, 1) = yr & "001"
    Else
        ws.Cells(LRow + 1, 1) = _
        yr & Format(Int(Right(ws.Cells(LRow, 1), 3)) + 1, "000")
    End If

End Sub
```


----------



## Hitman51 (Dec 11, 2022)

kevin9999 said:


> Try the following.  It assumes you won't have more than 999 new numbers each year.  Change "Sheet1" to your actual sheet name.
> 
> ```
> Private Sub CommandButton1_Click()
> ...


Thank you Kevin!


----------



## kevin9999 (Dec 11, 2022)

Hitman51 said:


> Thank you Kevin!


You're welcome, and thanks for the feedback 🙂


----------



## Hitman51 (Dec 13, 2022)

kevin9999 said:


> You're welcome, and thanks for the feedback





kevin9999 said:


> Try the following.  It assumes you won't have more than 999 new numbers each year.  Change "Sheet1" to your actual sheet name.
> 
> ```
> Private Sub CommandButton1_Click()
> ...


  I just noticed that the code provided above does not move to the next open row each time.  It just keeps rewriting over the last row in the spreadsheet.  Can someone please help as I need it to move to the next open row each time it is ran.


----------



## kevin9999 (Dec 13, 2022)

When I test the code you'd previously marked as the solution, I get this:

First run
Book1A1Header222-001345Sheet1

Second run
Book1A1Header222-001322-00245Sheet1

Third run
Book1A1Header222-001322-002422-0035Sheet1

As you can see, the code clearly moves to the next open row at each run.  That's because of the *+1* part of the LRow *+1* part of the code which, after it finds the last row with a value (LRow) moves down by one row (+1) before it writes the next sequential number.  If it is not/no longer working for you, it must be due to some other part of your project affecting the code.  If you share your file via Dropbox, Google Drive or something similar, I will gladly take a look at it to try and determine where the problem lies.


----------



## Hitman51 (Dec 14, 2022)

kevin9999 said:


> When I test the code you'd previously marked as the solution, I get this:
> 
> First run
> Book1A1Header222-001345Sheet1
> ...


Kevin,  I am not sure what is going on, but every time I run the routine from the dashboard (click the "Create RMA Button") it just overwrites the previous line I have in there.   Please let me know your thoughts.  Dummy file is here.









						2023 RMA V2.7.16_Dummy File.xlsm
					

Shared with Dropbox




					www.dropbox.com


----------



## kevin9999 (Dec 14, 2022)

I see what the problem is.  The code is finding the last used cell in column A - "LRow = ws.Cells(Rows.Count, *1*).End(xlUp).Row" - the 1 is referring to column 1 (=column A).
_As a point of interest, if you widen column A on the RMA Info-Status sheet, you can see where the code has actually been putting the correct codes in._

From what I can see, you actually want the new sequential codes written into column E?  Try changing the code below (look at the red *5*'s) and let me know how it goes.


```
'Inserts RMA# based on Year and Number (YY-### format)
    Dim ws As Worksheet, LRow As Long, yr As String
    Set ws = Worksheets("RMA Info-Status")
    LRow = ws.Cells(Rows.Count, *5*).End(xlUp).Row
    yr = Format(Date, "yy") & "-"
   
    'Test if first entry (assumes that row 1 contains headers)
    If LRow = 1 Then
       
        ws.Cells(LRow + 1, *5*) = yr & "001"
        Exit Sub
    End If
   
    'If this is not the first entry...
    If Left(ws.Cells(LRow, *5*), 3) <> yr Then
        ws.Cells(LRow + 1, *5*) = yr & "001"
    Else
        ws.Cells(LRow + 1, *5*) = _
        yr & Format(Int(Right(ws.Cells(LRow, *5*), 3)) + 1, "000")
    End If
```


----------



## Hitman51 (Dec 14, 2022)

kevin9999 said:


> I see what the problem is.  The code is finding the last used cell in column A - "LRow = ws.Cells(Rows.Count, *1*).End(xlUp).Row" - the 1 is referring to column 1 (=column A).
> _As a point of interest, if you widen column A on the RMA Info-Status sheet, you can see where the code has actually been putting the correct codes in._
> 
> From what I can see, you actually want the new sequential codes written into column E?  Try changing the code below (look at the red *5*'s) and let me know how it goes.
> ...


Thanks Kevin.  I should've caught that.  I apologize for not speaking up about it being column E.  Thanks again for your help.


----------



## kevin9999 (Dec 14, 2022)

Hitman51 said:


> Thanks Kevin.  I should've caught that.  I apologize for not speaking up about it being column E.  Thanks again for your help.


Glad we got to the bottom of it, and thanks for the feedback 🙂


----------

