Inputting Items and Descriptions into database

[Jetx227]

Hey guys! You have been a big help so far but I got another question (not surprisingly).
So I have this worksheet that has a database of about 20,000 items and on occasion we will have to input new ones. The items are organized by item number (column A) in numerical order. They also have a bunch of different values associated to them (columns B through L) but the most important ones are item number (column A), units (column E) and description (column L). We want to put in a row on top of the list (input in row 2, list would start at 7, headers at 5) where we can fill in the 3 main pieces we need filled out, item number (column A), units (column E) and description (column L), click a button, and have it automatically create a new row (based on numerical order) and input the values we entered. If there's a way for it to double check and see if the item already exists (so we don't add duplicates by mistake), that would be awesome, but its not necessary. Also we need column A to be formatted as text (even though we are inputting numbers). Any help you guys can offer would be much appreciated, thanks!

 

[JoeMo]

This should fix your problem - no need to enter A2 with an apostrophe now, but if you do that's ok too.

Sub Button1_Click()
Dim lR As Long, Inp As Range, n As Variant, R As Range, c As Range
lR = Range("A" & Rows.Count).End(xlUp).Row
Set Inp = Range("A2:L2")
If IsEmpty(Range("A2")) Then
    MsgBox "Please enter an Item # in A2 - then try again"
    Exit Sub
End If
n = Application.CountIf(Range("A7:A" & lR), Range("A2").Value)
If n > 0 Then
    MsgBox "The Item# you entered in A2 is already in the database"
    Exit Sub
End If
Application.ScreenUpdating = False
With Range("A" & lR + 1, "L" & lR + 1)
    .Cells(1, 1).NumberFormat = "@"
    .Value = Inp.Value
End With
Set R = Range("A7").CurrentRegion
Columns(1).Insert
For Each c In R.Columns(1).Offset(0, -1).Cells
    c.Value = Val(c.Offset(0, 1))
Next c
Range("A7").CurrentRegion.Sort key1:=Range("A7"), order1:=xlAscending
Columns(1).Delete
Application.ScreenUpdating = True
End Sub

 

[Jetx227]

This should fix your problem - no need to enter A2 with an apostrophe now, but if you do that's ok too.

Sub Button1_Click()
Dim lR As Long, Inp As Range, n As Variant, R As Range, c As Range
lR = Range("A" & Rows.Count).End(xlUp).Row
Set Inp = Range("A2:L2")
If IsEmpty(Range("A2")) Then
    MsgBox "Please enter an Item # in A2 - then try again"
    Exit Sub
End If
n = Application.CountIf(Range("A7:A" & lR), Range("A2").Value)
If n > 0 Then
    MsgBox "The Item# you entered in A2 is already in the database"
    Exit Sub
End If
Application.ScreenUpdating = False
With Range("A" & lR + 1, "L" & lR + 1)
    .Cells(1, 1).NumberFormat = "@"
    .Value = Inp.Value
End With
Set R = Range("A7").CurrentRegion
Columns(1).Insert
For Each c In R.Columns(1).Offset(0, -1).Cells
    c.Value = Val(c.Offset(0, 1))
Next c
Range("A7").CurrentRegion.Sort key1:=Range("A7"), order1:=xlAscending
Columns(1).Delete
Application.ScreenUpdating = True
End Sub

It did format the cell as text, but the green triangle that should pop up on the top left corner of the cell doesn't (and I need it to otherwise our other sheets won't update for some reason) unless I scroll down to the cell and click into it then out of it. Is there a way to make it so I don't have to do this?

 

[Jetx227]

I figured it out, all I did was input

Range("A" & lR + 1).Value = "'" & Range("A" & lR + 1).Value

into the With statement.

Thanks for your help!