Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting cells
I have a list that is organized alphabetically in a column. I want the user
to enter an item to add to the list in a text box, then click a button to have the text box information added to my list. How do I INSERT the information into the list in alphabetical order and keep all original list data? Then, how would I go about deleting an item in the same manner? -- Tyrell Fickenscher Plant Manager / Agronomist |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting cells
The simplest way would be to insert the new data at the bottom of
the list and resort. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tyrell" wrote in message ... I have a list that is organized alphabetically in a column. I want the user to enter an item to add to the list in a text box, then click a button to have the text box information added to my list. How do I INSERT the information into the list in alphabetical order and keep all original list data? Then, how would I go about deleting an item in the same manner? -- Tyrell Fickenscher Plant Manager / Agronomist |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting cells
I also think Chip's way is the simplest.
this one is not simple way. besides, not case sensitive. and the list must always be alphabetically ascending order. about deleting list, i'm not sure your way, but i assumed to delete only when match. in both cases, presuming the list is in colunm A Sub insertlist() Dim strin, s Dim stcell As Range, lsrange As Range Const cl = "a" '<<== column data in and Change here On Error Resume Next strin = Application.InputBox("input data", 1 + 2) If VarType(strin) = vbBoolean Then Exit Sub End If Set stcell = Cells(1, cl) Set lscell = Cells(Cells(Cells.Rows.count, cl).End(xlUp).Row, cl) s = Application.Match(strin, Range(stcell, lscell)) If Not IsError(s) Then stcell(s + 1).EntireRow.Insert stcell(s + 1) = strin Else stcell.EntireRow.Insert stcell(0) = strin End If End Sub Sub deletelist() Dim strin, s Dim stcell As Range, lsrange As Range Const cl = "a" '<<== column data in and Change here On Error Resume Next strin = Application.InputBox("input data", 1 + 2) If VarType(strin) = vbBoolean Then Exit Sub End If Set stcell = Cells(1, cl) Set lscell = Cells(Cells(Cells.Rows.count, cl).End(xlUp).Row, cl) s = Application.Match(strin, Range(stcell, lscell), 0) If Not IsError(s) Then stcell(s).EntireRow.Delete Else MsgBox "data[ " & strin & " ] not found" End If End Sub keizi "Tyrell" wrote in message ... I have a list that is organized alphabetically in a column. I want the user to enter an item to add to the list in a text box, then click a button to have the text box information added to my list. How do I INSERT the information into the list in alphabetical order and keep all original list data? Then, how would I go about deleting an item in the same manner? -- Tyrell Fickenscher Plant Manager / Agronomist |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Blocked from Inserting individual cells or Insert Cut Cells | Excel Discussion (Misc queries) | |||
inserting clip art into cells | Excel Discussion (Misc queries) | |||
Inserting cells between cells with text | Excel Discussion (Misc queries) | |||
Inserting text to certain cells | Excel Programming | |||
Inserting HTML into cells | Excel Programming |