Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill cells with names from list
XL2000
I have a workbook I want to use in multiple counties. I have a list of student names in a named range "Students" in ColAD ColA contains each students name followed by a list of classes followed by the words "Total Class Hours" I know I could do this: 1) Get a list of students from each county 2) Plug it into my existing workbook in ColAD 3) Reassign the named range "Students" to the new list 4) Select a name from the new list 5) Edit Copy 6) Scroll to and select an existing name in ColA 7) Edit PasteSpecial Values (to preserve ColA's formatting) 8) Repeat for each name in the new list Tedious, right? I'm hoping a macro could do this: 1) Pick a new name from "Students" 2) Place the name in ColA below the words "Total Class Hours" 3) Repeat for the rest of the new names in "Students" I know this would bypass the first "old" name. but I could just delete that range. Any help? -- David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill cells with names from list
SubCopyNames()
Dim rng as Range, rng1 as Range set rng = Range("Names") set rng1 = columns(1).Find(What:="Total Class Hours", _ After:=Range("IV65536"), _ LookIn:=xlConstants, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if rng1 is nothing then msgbox "Total Class Hours not found" Exit sub End if rng.copy rng1.offset(1,0) End Sub -- Regards, Tom Ogilvy "David" wrote in message ... XL2000 I have a workbook I want to use in multiple counties. I have a list of student names in a named range "Students" in ColAD ColA contains each students name followed by a list of classes followed by the words "Total Class Hours" I know I could do this: 1) Get a list of students from each county 2) Plug it into my existing workbook in ColAD 3) Reassign the named range "Students" to the new list 4) Select a name from the new list 5) Edit Copy 6) Scroll to and select an existing name in ColA 7) Edit PasteSpecial Values (to preserve ColA's formatting) 8) Repeat for each name in the new list Tedious, right? I'm hoping a macro could do this: 1) Pick a new name from "Students" 2) Place the name in ColA below the words "Total Class Hours" 3) Repeat for the rest of the new names in "Students" I know this would bypass the first "old" name. but I could just delete that range. Any help? -- David |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill cells with names from list
Well, that was interesting
After I corrected SubCopyNames() to Sub CopyNames() and changed Range("Names") to Range("Students"), the code bombed immediately with "Run-time error '1004': Unable to get the Find property of the Range class" I suspected the line "After:=Range("IV65536"), _" only because I didn't understand it, so I deleted it, No more error, but the code put ALL the new names after the first instance of "Total Class Hours" There are as many of those as there are students I probably didn't explain my layout clearly enough: ColumnA Student1 Name Class Class Class Class Total Class Hours Student2 Name Class Class Class Class Total Class Hours Student3 Name Class Class Class Class Total Class Hours and so on... Care to take another shot at it? The class list for each student occupies the same number of rows, so the number of rows between the Student's name and Total Class Hours is identical for each student, if that helps. -- David Tom Ogilvy wrote SubCopyNames() Dim rng as Range, rng1 as Range set rng = Range("Names") set rng1 = columns(1).Find(What:="Total Class Hours", _ After:=Range("IV65536"), _ LookIn:=xlConstants, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if rng1 is nothing then msgbox "Total Class Hours not found" Exit sub End if rng.copy rng1.offset(1,0) End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill cells with names from list
Just to elaborate, here's what I want to happen:
Get the 1st name out of Range("Students") Copy it after the 1st instance of "Total Class Hours" Get the 2nd name out of Range ("Students") Copy it after the 2nd instance of "Total Class Hours" Get the 3rd name out of Range ("Students") Copy it after the 3rd instance of "Total Class Hours" and so on... until all new names are copied And remember, I want to preserve the format of each cell with a name in it (they are shaded light green) -- David Tom Ogilvy wrote Subject: Fill cells with names from list From: "Tom Ogilvy" <<snip |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill cells with names from list
Assumes all processing is done on the activesheet. code has been tested and works exactly as I designed it. Sub CopyNames() Dim rng As Range, rng1 As Range Dim cnt As Long, cnt1 As Long Dim i As Long, sAddr As String Dim rng2 As Range Set rng = Range("Students") cnt = Application.CountIf(Columns(1), "*Total Class Hours*") cnt1 = Application.CountA(rng) If cnt1 cnt Then MsgBox "Only " & cnt & " Students will be processed" End If Set rng1 = Columns(1).Find(What:="Total Class Hours", _ After:=Range("A65536"), _ LookIn:=xlConstants, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng1 Is Nothing Then i = 1 sAddr = rng1.Address Do If i = 1 Then ' Change A2 to the cell to get the ' first name Range("A2").Value = rng(i) Else rng2.Offset(1, 0).Value = rng(i) End If i = i + 1 Set rng2 = rng1 Set rng1 = Columns(1).FindNext(rng2) Loop Until i cnt Or rng1.Address = sAddr End If End Sub -- Regards, Tom Ogilvy "David" wrote in message ... Just to elaborate, here's what I want to happen: Get the 1st name out of Range("Students") Copy it after the 1st instance of "Total Class Hours" Get the 2nd name out of Range ("Students") Copy it after the 2nd instance of "Total Class Hours" Get the 3rd name out of Range ("Students") Copy it after the 3rd instance of "Total Class Hours" and so on... until all new names are copied And remember, I want to preserve the format of each cell with a name in it (they are shaded light green) -- David Tom Ogilvy wrote Subject: Fill cells with names from list From: "Tom Ogilvy" <<snip |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill cells with names from list
Perfect!!! You've come through for me again. Many thanks.
-- David Tom Ogilvy wrote Subject: Fill cells with names from list From: "Tom Ogilvy" Newsgroups: microsoft.public.excel.programming Assumes all processing is done on the activesheet. code has been tested and works exactly as I designed it. Sub CopyNames() Dim rng As Range, rng1 As Range Dim cnt As Long, cnt1 As Long Dim i As Long, sAddr As String Dim rng2 As Range Set rng = Range("Students") cnt = Application.CountIf(Columns(1), "*Total Class Hours*") cnt1 = Application.CountA(rng) If cnt1 cnt Then MsgBox "Only " & cnt & " Students will be processed" End If Set rng1 = Columns(1).Find(What:="Total Class Hours", _ After:=Range("A65536"), _ LookIn:=xlConstants, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng1 Is Nothing Then i = 1 sAddr = rng1.Address Do If i = 1 Then ' Change A2 to the cell to get the ' first name Range("A2").Value = rng(i) Else rng2.Offset(1, 0).Value = rng(i) End If i = i + 1 Set rng2 = rng1 Set rng1 = Columns(1).FindNext(rng2) Loop Until i cnt Or rng1.Address = sAddr End If End Sub -- Regards, Tom Ogilvy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I fill blank cells in a list with previous filled value? | Excel Worksheet Functions | |||
Need a drop down list to auto fill other cells... | Excel Discussion (Misc queries) | |||
Help! I want to fill cells as a result of selecting a value from a list in another cell. | Excel Worksheet Functions | |||
Creating a drop down list that will fill in mutilple cells.... | Excel Discussion (Misc queries) | |||
Auto Fill Cells, When Choosing From Drop-Down List... | Excel Worksheet Functions |