Excel Programming to automatically fill in partial input (numbers)

Question

We manage our stocks in Excel. I know him a little old-fashioned, but we are developing a business firm, and we have all our money blocked in business, and there is no money for investment in IT.

So, I wanted to know if I can program in such a way that excel automatically complements product numbers?

This is an example of one product category.

All of our design codes are 6 digits. I really want that when adding only a partial number and clicking on it, it automatically completes the remaining digits, taking the above numbers.

So, for example, in this case, I expect that if I type 5hit enter, it will automatically make it 790705based on the number above.

Answer 1

VBA :

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oldText  As String, aboveText As String, newText As String
    If Target.Column = 2 And Target.Row >= 3 And Target.Text <> "" Then
        oldText = Target.Text
        aboveText = Target.Cells(0, 1).Text
        If Len(aboveText) = 6 And Len(oldText) < 6 Then
            newText = Left(aboveText, 6 - Len(oldText)) & oldText
            Application.EnableEvents = False
                Target.Value = newText
            Application.EnableEvents = True
        End If
    End If
End Sub

( / ).

Answer 2

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   If (Target.Column = 2 And Target.Row > 2 And Target.Value < 10) Then
       Target = Target.Offset(rowOffset:=-1) + 1
   End If
End Sub

, + 1.