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]

How would a duplicate be identified - by Item # or ......?

 

[Jetx227]

How would a duplicate be identified - by Item # or ......?

Yes by item number, some of the items have the same or similar description and units.

 

[JoeMo]

Can you post some sample data and layout so we can see what the item "numbers" that are text look like?

 

[Jetx227]

Can you post some sample data and layout so we can see what the item "numbers" that are text look like?

I'm not sure how to link an excel sheet but if you wanna test it in a sheet I can just tell you what we do. Basically we either format the cell as text FIRST before we put the number in (for some reason it doesn't work when you format it after) or, after you put the number in you put an apostrophe in front of it. Then you get this little green triangle that pops up in the corner of the cell.

 

[JoeMo]

You haven't given me much to work with so I've made some assumptions, including that you have already formatted col A as text. The code below is untested. Try it on a copy of your data. Run it from a forms control command button - change the button name to match the one on your sheet.

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)
    .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]

You haven't given me much to work with so I've made some assumptions, including that you have already formatted col A as text. The code below is untested. Try it on a copy of your data. Run it from a forms control command button - change the button name to match the one on your sheet.

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)
    .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

Great! This worked very well, it inputted the number perfectly, although the cell in column "A" did not keep its text formatting, so can I just adjust the code to add an apostrophe before it? Also, if I just change the line that ends with "xlAscending" to "xlDescending" will that change it to descending numerical order?

 

[Jetx227]

Great! This worked very well, it inputted the number perfectly, although the cell in column "A" did not keep its text formatting, so can I just adjust the code to add an apostrophe before it? Also, if I just change the line that ends with "xlAscending" to "xlDescending" will that change it to descending numerical order?

Sub Button1_Click()
Dim lR As Long, Inp As Range, n As Variant, R As Range, c As Range, Z As Range
Set Z = Range("A2")
If Z.Value <> "" Then Z.Value = "'" & Z.Value
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)
    .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:=xlDescending
Columns(1).Delete
Application.ScreenUpdating = True
End Sub

So this is my updated code, and now it does add the apostrophe to cell A2 for the item number but it doesn't copy it down when it adds it to the database. Any thoughts?

 

[JoeMo]

Sub Button1_Click()
Dim lR As Long, Inp As Range, n As Variant, R As Range, c As Range, Z As Range
Set Z = Range("A2")
If Z.Value <> "" Then Z.Value = "'" & Z.Value
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)
    .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:=xlDescending
Columns(1).Delete
Application.ScreenUpdating = True
End Sub

So this is my updated code, and now it does add the apostrophe to cell A2 for the item number but it doesn't copy it down when it adds it to the database. Any thoughts?

What is "it" - the apostrophe or ...?. Why not format the whole column A as text before running any code then you won't need the apostrophe in A2 or any other col A cell?

 

[Jetx227]

What is "it" - the apostrophe or ...?. Why not format the whole column A as text before running any code then you won't need the apostrophe in A2 or any other col A cell?

Yes "it" is the apostrophe, and I don't want to change the formatting of the whole column because then it messes with the number. If you click on the cells, you get this little pop up next to it that tells you that excel is viewing the number as text or it has an apostrophe in front of it. For some reason, it'll only do that if I update the cells to be text before I put the number in (so if I update the whole column it'll mess with the current items in place) or if I have the apostrophe in front of it. So it's just easier to have the apostrophe attached to it. And in case you were wondering this too, I need it to be formatted that way so that one of the other sheets can pull the items from this one correctly. If they aren't formatted like this it doesn't work.