Better way to save a starting location in a macro?

[andyfleisher]

I am using data like the example below and created a macro to take the course information and append it to the left of each student and then delete any extra information to just end up with a single table. The original data is an export from our school's roster system, so it may be a single course, or multiple. In the code I created, I insert some columns and then start cycling through each course using the Find command. I know that Fine will eventually loop back to the start of the worksheet, so what is the best way for the loop to stop? Currently, I run my code once (since there will be at least a single course) and then to a Do...Loop to go through the rest of the data. I save the first cell address that is found into a variable and check against that each time the loop runs. Once the Find command circles back to the start of the worksheet, and "finds" the initial cell again, the loop will exit and continue to the next part of the code.

It seems redundant to rune the code once just to be able to save a starting point. Is there a better way?

The data starts out like this:

Online MBA cross registered.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	TITLE	CRN	TERM	SUBJ	CRSE	SEC	CREDITS
2	Elementary Accounting	41072	202110	MGT	011A	B05	4
3
4	INSTRUCTOR(S)	TYPE	DAYS	TIME	BUILD	ROOM
5	Thomas, Derek Raymond James	Lecture	TR	1:40 PM - 3:00 PM	HARING	2205
6		Discussion	F	2:10 PM - 3:00 PM	SOCSCI	80
7	Seq	SID	Name		Level	Units	Class	Major	Grade	Status	Status Date	Email
8			Last	PreferredName
9	2	Student ID	Last Name	First Name	UG	4	SO	BBMB		RE	44326.625	Email
10	4	Student ID	Last Name	First Name	UG	4	SO	LIRE		RE	44336.47986	Email
11	5	Student ID	Last Name	First Name	UG	4	SO	BBIS		RE	44336.52083	Email
12	6	Student ID	Last Name	First Name	UG	4	SO	ECIV		RE	44336.54167	Email
13	9	Student ID	Last Name	First Name	UG	4	FR	BBIS		RE	44430.66319	Email
14
15	5 students printed
16
17	Ran on 10/15/2021 10:03 AM
18
19
20
21	TITLE	CRN	TERM	SUBJ	CRSE	SEC	CREDITS
22	Elementary Accounting	41073	202110	MGT	011A	B06	4
23
24	INSTRUCTOR(S)	TYPE	DAYS	TIME	BUILD	ROOM
25	Thomas, Derek Raymond James	Lecture	TR	1:40 PM - 3:00 PM	HARING	2205
26		Discussion	F	3:10 PM - 4:00 PM	WELLMN	233
27	Seq	SID	Name		Level	Units	Class	Major	Grade	Status	Status Date	Email
28			Last	PreferredName
29	3	Student ID	Last Name	First Name	UG	4	FR	AMGE		RE	44336.47917	Email
30	4	Student ID	Last Name	First Name	UG	4	SO	LECN		RE	44336.54167	Email
31	5	Student ID	Last Name	First Name	UG	4	SO	BBMB		RE	44336.6875	Email
32
33	3 students printed
34
35	Ran on 10/15/2021 10:03 AM
Working

The code in question is below. I start the macron with the selection in cell A1 and let it run from there. This part takes the three cells from under the SUBJ, CRSE and SEC part in the course header and pastes it to the left of each student. Then the loop does the find command again and repeats the process until it hits that initial starting cell and then ends. I kept trying to figure out a better way to save, and check for that starting cell but this was the best I could do. The overall code does it's job, and it's not something I will use a ton but I am still learning, so I'm sure someone has a better solution. Thanks in advance!

'Cycle once through the process
    Cells.Find(What:="SUBJ", After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    CheckCell = ActiveCell.Address()
    ActiveCell.Offset(1, 0).Range("A1:C1").Select
    Selection.Copy
    Selection.End(xlToLeft).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Range(ActiveCell, ActiveCell.End(xlDown)).Offset(0, -3).Select
    ActiveSheet.Paste
    Selection.End(xlDown).Select
'Start the loop, loop should exit if only one iteration.
    Do
    Cells.Find(What:="SUBJ", After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    If ActiveCell.Address() = CheckCell Then
        Exit Do
    End If
    ActiveCell.Offset(1, 0).Range("A1:C1").Select
    Selection.Copy
    Selection.End(xlToLeft).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Range(ActiveCell, ActiveCell.End(xlDown)).Offset(0, -3).Select
    ActiveSheet.Paste
    Selection.End(xlDown).Select
    Loop

 

[ranman256]

either hard code the path in the code,
or (what I do) store the path in a cell in a hidden sheet.

 

[andyfleisher]

Thanks. I used Offset to replace a bunch of the xlDown actions and just left things as is.