VBA Adding Data to Columns From Above

[Stephen_IV]

I have data that comes off of an older program, like below is it possible to add in the school name and the teacher name (that is above the data) to the first row in the data like below?

Thanks in advance Stephen

Book1
	A	B	C	D	E	F	G
1		Lincoln-Rm: B223, Mrs. Y
2
3	Date			User Name	ID	Test Name	Score
4
5	3/25/2009			xxxxxxx xxxxx	299287177	Reading - Grade 3 Marking Period 3	24
6
7	3/26/2009			xxxxxxx xxxxx	299287179	Reading - Grade 3 Marking Period 3	48
8
9	3/27/2009			xxxxxxx xxxxx	299287181	Reading - Grade 3 Marking Period 3	69
10
11	3/25/2009			xxxxxxx xxxxx	299287183	Reading - Grade 3 Marking Period 3	55
12
13	3/25/2009			xxxxxxx xxxxx	299287185	Reading - Grade 3 Marking Period 3	67
14
15	3/25/2009			xxxxxxx xxxxx	299287187	Reading - Grade 3 Marking Period 3	48
16
17	3/25/2009			xxxxxxx xxxxx	299287189	Reading - Grade 3 Marking Period 3	67
18
19	3/25/2009			xxxxxxx xxxxx	299287191	Reading - Grade 3 Marking Period 3	14
20
21	3/25/2009			xxxxxxx xxxxx	299287193	Reading - Grade 3 Marking Period 3	60
22
23	3/25/2009			xxxxxxx xxxxx	299287195	Reading - Grade 3 Marking Period 3	31
24
25	3/25/2009			xxxxxxx xxxxx	299287197	Reading - Grade 3 Marking Period 3	40
26
27
28
29		Washington-Rm: 305, Mr X
30
31	Date			User Name	ID	Test Name	Score
32
33	3/25/2009			xxxxxxx xxxxx	299287198	Reading - Grade 3 Marking Period 3	64
34
35	3/25/2009			xxxxxxx xxxxx	299287200	Reading - Grade 3 Marking Period 3	64
36
37	3/25/2009			xxxxxxx xxxxx	299287202	Reading - Grade 3 Marking Period 3	83
38
39	3/25/2009			xxxxxxx xxxxx	299287204	Reading - Grade 3 Marking Period 3	62
Sheet1

to this

Book1
	A	B	C	D	E	F	G
3	Date			User Name	ID	Test Name	Score
4
5	3/25/2009	Lincoln	Mrs. Y	xxxxxxx xxxxx	299287177	Reading - Grade 3 Marking Period 3	24
6
7	3/26/2009			xxxxxxx xxxxx	299287179	Reading - Grade 3 Marking Period 3	48
8
9	3/27/2009			xxxxxxx xxxxx	299287181	Reading - Grade 3 Marking Period 3	69
10
11	3/25/2009			xxxxxxx xxxxx	299287183	Reading - Grade 3 Marking Period 3	55
12
13	3/25/2009			xxxxxxx xxxxx	299287185	Reading - Grade 3 Marking Period 3	67
14
15	3/25/2009			xxxxxxx xxxxx	299287187	Reading - Grade 3 Marking Period 3	48
16
17	3/25/2009			xxxxxxx xxxxx	299287189	Reading - Grade 3 Marking Period 3	67
18
19	3/25/2009			xxxxxxx xxxxx	299287191	Reading - Grade 3 Marking Period 3	14
20
21	3/25/2009			xxxxxxx xxxxx	299287193	Reading - Grade 3 Marking Period 3	60
22
23	3/25/2009			xxxxxxx xxxxx	299287195	Reading - Grade 3 Marking Period 3	31
24
25	3/25/2009			xxxxxxx xxxxx	299287197	Reading - Grade 3 Marking Period 3	40
26
27
28
29
30
31	Date			User Name	ID	Test Name	Score
32
33	3/25/2009	Washington	Mr X	xxxxxxx xxxxx	299287198	Reading - Grade 3 Marking Period 3	64
34
35	3/25/2009			xxxxxxx xxxxx	299287200	Reading - Grade 3 Marking Period 3	64
36
37	3/25/2009			xxxxxxx xxxxx	299287202	Reading - Grade 3 Marking Period 3	83
38
39	3/25/2009			xxxxxxx xxxxx	299287204	Reading - Grade 3 Marking Period 3	62
Sheet1

 

[MickG]

Try this:-
NB:- Note the "Remark" in code.

[COLOR="Navy"]Sub[/COLOR] MG20May07
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Title [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Name [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn(, 2) <> "" [COLOR="Navy"]Then[/COLOR]
        Name = Split(Dn(, 2), "-")(0)
        Title = Split(Dn(, 2), ",")(1)
        Dn(, 2) = "" '[COLOR="Green"][B]Remove this line if you want to keep the original[/B][/COLOR]
        '[COLOR="Green"][B]Text in column "B"[/B][/COLOR]
    [COLOR="Navy"]ElseIf[/COLOR] IsDate(Dn) [COLOR="Navy"]Then[/COLOR]
        Dn(, 2) = Name: Dn(, 3) = Title
        Name = ""
        Title = ""
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Stephen_IV]

Thank you so Much Mick! Works great always admired your posts!

 

[Rumble]

Great timing, I have a similar related question. Assume that the data has been grouped similar to the poster's example where there is a value that I would like to copy to EVERY row within that group... the existing data looks like this:

A B C D E
1. BLUE
2. Ball
3. Pale
4. Book
5.
6. Number Blue=3
7.
8. RED
9. Sign
10. Cup
11. Door
12. Car
13. Poster
14.
15. Number Red=5

To now have my data have a 1-to-1 association and remove the grouping to perform pivots and other preferred ways to use the data, the final output would look like this essentially removing the grouping and label each row with the previous group value:

A B C D E
1. BLUE Ball
2. BLUE Pale
3. BLUE Book
4. RED Sign
5. RED Cup
6. RED Door
7. RED Car
8. RED Poster