I'm Back --- New Stand Alone Question -Insert Name in List

[want2b]

The easy way would be to insert at the bottom and then sort BUT I don't think this will work because:
I have 55 sheets the last of which totals for all the others.
I need to keep the Names in the same row in each of the 55 sheets.
I search for an account number if not found it asks for then looks for last names, when it finds one it asks if it should look for another. This all works great, to improve I would like to have it insert in ABC order the name if it is not already in the list. I have last names in column A and First Name MI in column B.

I can do this manually, but thanks to you guys this is becoming a slick program, and if it can be done, why not?

Thanks, and as many of you know I am dense, so please be kind.

 

[tactps]

Re: I'm Back --- New Stand Alone Question -Insert Name in Li

I think you are losing the term "NewName" because you are switching from one macro to the next.

Try this:
[A1].Select
NewName = InputBox("New Name Needed", "Enter the Name")

Range("A3").Select
Do While True
If Mid(NewName, 1, 1) > Mid(ActiveCell.Value, 1, 1) Then ActiveCell.Offset(1, 0).Select

If Mid(NewName, 1, 1) = Mid(ActiveCell.text, 1, 1) Then
Do While True

If Mid(NewName, 2, 1) > Mid(ActiveCell.Text, 2, 1) Then ActiveCell.Offset(1, 0).Select

Loop
End If
End
End
Loop
End Sub

 

[tactps]

Re: I'm Back --- New Stand Alone Question -Insert Name in Li

Just one more possibility:

Use the following as the macro to find the row to insert. myVar will be the row number to select.

Sub FindRow()
NewName = InputBox("New Name Needed", "Enter the Name")
Range("a3").Select
myVar = Application.WorksheetFunction _
.Match(NewName, Worksheets("Worksheet").Range("A1:A1000"), 0)

End S ub

 

[want2b]

Re: I'm Back --- New Stand Alone Question -Insert Name in Li

The ActiveCell.Text did not work either at least for the Second Letter I did not try it yet for the first.

Good Idea and Thanks

tactps
yours went back and asked me to enter a name again.
The reason I have subs in the middle is because I do not know how to do
loops so by breaking it down into subs I can almost understand what is happening.

What I really think I need now is something that would return
what Lotus 1-2-3 would return with
@CELLPOINTER("Contents")

This may not even be doable in Excel.

 

[tactps]

Re: I'm Back --- New Stand Alone Question -Insert Name in Li

Sorry, put 1) not 0) in the "match" line

 

[want2b]

Re: I'm Back --- New Stand Alone Question -Insert Name in Li

I warned at the beginning that I am DENSE...

I see what you mean now about loosing the NewName. I need to make that GLOBAL or something so it will go from Sub to Sub

Not sure how to do that.

 

[want2b]

Re: I'm Back --- New Stand Alone Question -Insert Name in Li

tactps
I get an error
Compile Error
Syntax error
with Sub FindRow()
I did change the 0 to 1

It didn't direct me to the error location and I have not a clue.

 

[tactps]

Re: I'm Back --- New Stand Alone Question -Insert Name in Li

This should be the whole macro (to insert the line):

Sub FindRow()
NewName = InputBox("New Name Needed", "Enter the Name")

Range("a3").Select
myVar = Application.WorksheetFunction _
.Match(NewName, Worksheets("Worksheet").Range("A1:A1000"), 1) + 1 'This finds the row number 'Ensure that the name "worksheet" is changed to the worksheet that you want to search (eg "Sheet1")
Sheets(Array("Lookups", "Worksheet", "Buying Groups", "Banner Groups")).Select 'Record this from sheet 1 to 55
Rows(myVar).Select
Selection.Insert Shift:=xlDown
ActiveCell.Formula = NewName

End Sub[/b]