Better way to save a starting location in a macro?

andyfleisher

New Member
Joined
May 25, 2011
Messages
4
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
ABCDEFGHIJKL
1TITLECRNTERMSUBJCRSESECCREDITS
2Elementary Accounting41072202110MGT011AB054
3
4INSTRUCTOR(S)TYPEDAYSTIMEBUILDROOM
5Thomas, Derek Raymond JamesLectureTR1:40 PM - 3:00 PMHARING2205
6DiscussionF2:10 PM - 3:00 PMSOCSCI80
7SeqSIDNameLevelUnitsClassMajorGradeStatusStatus DateEmail
8LastPreferredName
92Student IDLast NameFirst NameUG4SOBBMBRE44326.625Email
104Student IDLast NameFirst NameUG4SOLIRERE44336.47986Email
115Student IDLast NameFirst NameUG4SOBBISRE44336.52083Email
126Student IDLast NameFirst NameUG4SOECIVRE44336.54167Email
139Student IDLast NameFirst NameUG4FRBBISRE44430.66319Email
14
155 students printed
16
17Ran on 10/15/2021 10:03 AM
18
19
20
21TITLECRNTERMSUBJCRSESECCREDITS
22Elementary Accounting41073202110MGT011AB064
23
24INSTRUCTOR(S)TYPEDAYSTIMEBUILDROOM
25Thomas, Derek Raymond JamesLectureTR1:40 PM - 3:00 PMHARING2205
26DiscussionF3:10 PM - 4:00 PMWELLMN233
27SeqSIDNameLevelUnitsClassMajorGradeStatusStatus DateEmail
28LastPreferredName
293Student IDLast NameFirst NameUG4FRAMGERE44336.47917Email
304Student IDLast NameFirst NameUG4SOLECNRE44336.54167Email
315Student IDLast NameFirst NameUG4SOBBMBRE44336.6875Email
32
333 students printed
34
35Ran 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!

VBA Code:
'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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
either hard code the path in the code,
or (what I do) store the path in a cell in a hidden sheet.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

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
Back
Top