Excel Programming for auto-complete of partial input (numbers)

We manage our inventories in Excel. I know its little old fashioned but we are developing business firm, and we have all our money blocked in business and no money to invest in IT.

So I wanted to know can I program in a way that excel automatically completes the product numbers?

This is example of one product category

All our design codes are of 6 digits, W开发者_开发百科hat I really want is that when only partial number is added and hit enter it automatically completes the remaining digits by taking the above numbers.

So for example in this case what I am expecting is, if I type 5 hit enter it automatically makes it 790705 based on above number.

Add the following VBA code to the code section of your worksheet:

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

(change the column number and minimum row number above according to the actual column/row numbers in your worksheet).

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

As far as you enter a single digit in the new row, it transforms to the previous row + 1.