Insert Name Alphabetically into Column with VBA

[ciavala]

Hello Everyone,

Need some help with some VBA. I want to take the value of an input box, which is going to be a name of a new employee, and then look down the column of employee names alphabetically and insert new row with new name. Is this possible?


Thanks
ciavala

 

[cornflakegirl]

Try this:

Sub InsertName()
Dim sNewName As String
Dim lPosition As Long
Dim rEmpList As Range

Set rEmpList = Sheets("Sheet1").Range("A:A") 'adjust as required

sNewName = InputBox("Enter name of new employee")
On Error Resume Next 'if employee needs to go at start of list, Match will return #NA
lPosition = Application.WorksheetFunction.Match(sNewName, rEmpList, 1)
On Error GoTo 0 'revert to normal error handling (crashing)
Rows(lPosition + 1).Insert
Range("A" & lPosition + 1).Value = sNewName

End Sub

 

[ciavala]

Works like a charm cornflakegirl. I appriciate the help.

Thanks
ciavala

 

[Omen666blue]

Thanks! Just found this thread and doing almost the exact same thing! give or take a few User prompts/Variables!

 

[rolexdude]

Try this:

Sub InsertName()
Dim sNewName As String
Dim lPosition As Long
Dim rEmpList As Range

Set rEmpList = Sheets("Sheet1").Range("A:A") 'adjust as required

sNewName = InputBox("Enter name of new employee")
On Error Resume Next 'if employee needs to go at start of list, Match will return #NA 
lPosition = Application.WorksheetFunction.Match(sNewName, rEmpList, 1)
On Error GoTo 0 'revert to normal error handling (crashing)
Rows(lPosition + 1).Insert
Range("A" & lPosition + 1).Value = sNewName

End Sub

Thanks for this, Cornflakegirl,

Can you modify it so that I can insert a cell that's not on column A and based on a named range? I tried to manipulate your code but with no luck. In my application, the named range is usually between C76:C115, but the column can change if an user insert a column between A&B or rows can be added, thus I like to use a named range.

 

[NdNoviceHlp]

Set rEmpList = Sheets("Sheet1").Range("YourNamedRange") 
Sheets("Sheet1").Rows(lPosition + 1).Insert
Sheets("Sheet1").Range("YourNamedRange" & lPosition + 1).Value = sNewName

HTH. Dave

 

[rolexdude]

Set rEmpList = Sheets("Sheet1").Range("YourNamedRange") 
Sheets("Sheet1").Rows(lPosition + 1).Insert
Sheets("Sheet1").Range("YourNamedRange" & lPosition + 1).Value = sNewName

HTH. Dave

Thank you for your reply, Dave. I had tried that before, but I get error of "400" when it tries to execute the very last line.

 

[Fluff]

Hi
Try

Sub InsertName()
Dim sNewName As String
Dim lPosition As Long
Dim rEmpList As Range

Set rEmpList = Range("names") 'adjust as required

sNewName = InputBox("Enter name of new employee")
On Error Resume Next 'if employee needs to go at start of list, Match will return [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NA]#NA[/URL] 
lPosition = Application.WorksheetFunction.Match(sNewName, rEmpList, 1)
On Error GoTo 0 'revert to normal error handling (crashing)
rEmpList(lPosition + 1).Insert
rEmpList(lPosition + 1).Value = sNewName

End Sub

 

[rolexdude]

Hi
Try

Sub InsertName()
Dim sNewName As String
Dim lPosition As Long
Dim rEmpList As Range

Set rEmpList = Range("names") 'adjust as required

sNewName = InputBox("Enter name of new employee")
On Error Resume Next 'if employee needs to go at start of list, Match will return [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NA"]#NA[/URL] 
lPosition = Application.WorksheetFunction.Match(sNewName, rEmpList, 1)
On Error GoTo 0 'revert to normal error handling (crashing)
rEmpList(lPosition + 1).Insert
rEmpList(lPosition + 1).Value = sNewName

End Sub

Thanks, Fluff. That worked great. How to format that cell the new value is just now added with a different shade of color? It makes it easier to find once it is inserted to a new row.

 

[Fluff]

Add this

rEmpList(lPosition + 1).Interior.color=vbred