Fill cells with names from list

[David]

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?

 

[Tom Ogilvy]

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

 

[David]

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]

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: Re: Fill cells with names from list
From: "Tom Ogilvy" <[email protected]>

<<snip>>

 

[Tom Ogilvy]

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

 

[David]

Perfect!!! You've come through for me again. Many thanks.