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

want2b

Board Regular
Joined
Jan 27, 2004
Messages
89
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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
Re: I'm Back --- New Stand Alone Question -Insert Name in Li

want2b said:
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.
Hi!
Im confused. You want to keep the name in the same row, but you want to
insert a new name so that it will be in ABC (sorted order).What happened to the other rows? they will be displaced!
if yuo just want them in order, why not just sort it?
:rolleyes:
 

want2b

Board Regular
Joined
Jan 27, 2004
Messages
89
Re: I'm Back --- New Stand Alone Question -Insert Name in Li

Good Question,

They need to stay in order for all the sheets.
Say I am into the 17th week of the year and need to insert someone
I link all the sheets and insert the row where needed. There will be no information on this row for the first 16 pages, but when I copy the formulars into this row for the 55th sheet all the infomation entered from sheets 17-54 will be summed. At the beginning of each week I copy the names ect to the next sheet. All the previous sheets will have blanks in the rows where info was added up to when it was added.
If you could sort 17 sheets at the same time that would work but I don't think you can do that. And in the 49th week a screw up would be...
Of course I have it backed up.
 

want2b

Board Regular
Joined
Jan 27, 2004
Messages
89
Re: I'm Back --- New Stand Alone Question -Insert Name in Li

Sub InsertName()
[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.Value, 1, 1) Then NextLetter2
Loop
End

End Sub
Sub NextLetter2()
Do While True
If Mid(NewName, 2, 1) > Mid(ActiveCell.Value, 2, 1) Then ActiveCell.Offset(1, 0).Select

If Mid(NewName, 2, 1) = Mid(ActiveCell.Value, 2, 1) Then NextLetter3
Loop
End
End Sub
Sub NextLetter3()

End Sub

This starts out working at least on the first letter of a name but when it looks for the second letter it doesn't see it.

Any Idea... And got a better way to do this... Must be plenty

Thanks
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459

ADVERTISEMENT

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

I am not sure if I'm reading this correctly, but this is my understanding:

You want to insert a row into the same place in all 55 sheets. In sheets 1-54 you may or may not put in data, but in sheet 55 you want to total.

If so:
Select all sheets
Insert a row (which will insert it into each sheet in the same spot)
Select the sheet 1-54 that you want to update and do so.
In sheet 55, copy your formulas into the new row.
 

want2b

Board Regular
Joined
Jan 27, 2004
Messages
89
Re: I'm Back --- New Stand Alone Question -Insert Name in Li

That's right and the way I am doing it now.
What I would like to do is type in a name and have the macro find the row it should go into and do it for me.
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459

ADVERTISEMENT

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

Doesn't the "do while true" allow you to then stop the macro at that point and then have the macro insert the rows for you, something like:

Rownumber = ActiveCell.Row
Sheets(Array("Sheet3", "Sheet2", "Sheet1")).Select
Rows(Rownumber).Select
Selection.Insert Shift:=xlDown

etc
 

want2b

Board Regular
Joined
Jan 27, 2004
Messages
89
Re: I'm Back --- New Stand Alone Question -Insert Name in Li

Yes that is where I am heading.

I've got a problem with it looking at the second letter.

THe Mid(NewName,2,1) > Mid(ActiveCell.Value,2,1) never becomes true

Where Mid(NewName 1,1)> Mid(ActiveCell.Value,1,1) Does and I can't
see the difference???
I changed from Left(NewName,1) and it still worked so why doesn't the 2,1 Work?

Could it be something with case? I tried using PROPER, Maybe UPPER
but am not sure how to use it in the way I am trying.
Thanks
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
Re: I'm Back --- New Stand Alone Question -Insert Name in Li

I haven't tested it, but perhaps you need to use activecell.text not activecell.value.
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
Re: I'm Back --- New Stand Alone Question -Insert Name in Li

Why have you got an end sub in the middle. I would have thought that this should be one macro.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,971
Messages
5,599,110
Members
414,289
Latest member
sonintebil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top