tjdickinson
Board Regular
- Joined
- Jun 26, 2021
- Messages
- 61
- Office Version
- 365
- Platform
- Windows
I am modifying timetables which have been exported from a separate application, but the way it displays the class names is suboptimal (and there's no way to change it in the application itself). I am building a macro to apply a number of formatting changes to the timetables quickly, but I am still quite novice when it comes to VBA.
In each sheet, lessons occupy one cell or two vertically merged cells. Each lesson has two lines displaying the course name and the classes present. For example:
I want this to display as:
(The reason for these changes is (a) reducing horizontal width of each cell so all columns fit comfortably on one page, and (b) some courses have many classes in them, resulting in truncated lines which don't look good.)
The code I have so far defines two corresponding arrays, such that index (x) in arrayA should be replaced by index (x) in arrayB. The final output (with one-ish exception) must retain the course identifier on the first line and replace only the class names on the second line.
Note: I am defining the array values in the code because I need this to run on the exported files from the timetable software. Thus, I'm not able to put the data into a separate sheet and reference it in the code.
Note: I have defined the range of the arrays as 1 To 20 (even though there are only 16 elements) to anticipate the possibility of adding more. If it's possible to make these arrays dynamic, all the better.
Challenges:
Thank you in advance for your help. My apologies in advance for not being able to respond again today--I'm leaving now for my godson's birthday party--but I will respond first thing tomorrow. Wishing you all a happy, safe, and healthy new year!
In each sheet, lessons occupy one cell or two vertically merged cells. Each lesson has two lines displaying the course name and the classes present. For example:
means the students in classes '4 HUM' and '4 LAT' are both in the same French course.
I want this to display as:
(The reason for these changes is (a) reducing horizontal width of each cell so all columns fit comfortably on one page, and (b) some courses have many classes in them, resulting in truncated lines which don't look good.)
The code I have so far defines two corresponding arrays, such that index (x) in arrayA should be replaced by index (x) in arrayB. The final output (with one-ish exception) must retain the course identifier on the first line and replace only the class names on the second line.
VBA Code:
Sub RenameClasses()
On Error GoTo errorcatch
Dim ClassNamesA(1 To 20) As String
Dim ClassNamesB(1 To 20) As String
Dim ClassReplace() As Variant
ClassNamesA(1) = "4 HUM 4 LAT"
ClassNamesB(1) = "4 ASO"
ClassNamesA(2) = "5 HUM 5 LAT-MT 6 LAT-MT"
ClassNamesB(2) = "3de Graad"
ClassNamesA(3) = "3 HUM 3 LAT"
ClassNamesB(3) = "3 ASO"
ClassNamesA(4) = "6 LAT-MT"
ClassNamesB(4) = "6 ASO"
ClassNamesA(5) = "5 HUM 5 LAT-MT"
ClassNamesB(5) = "5 ASO"
ClassNamesA(6) = "2A KT 2A MT-W"
ClassNamesB(6) = "2A"
ClassNamesA(7) = "1A1 1A2 2A MT-W 2A KT"
ClassNamesB(7) = "1ste Graad"
ClassNamesA(8) = "1A1 1A2 2A KT 2A MT-W"
ClassNamesB(8) = "1ste Graad"
ClassNamesA(9) = "3 HUM 3 LAT 4 HUM 4 LA" ' LO/BEZ
ClassNamesB(9) = "2de/3de Graad" ' LO/BEZ
ClassNamesA(10) = "3 HUM 6 LAT-MT 4 LAT 5 L" ' LO
ClassNamesB(10) = "2de/3de Graad" ' LO
ClassNamesA(11) = "3 HUM 3 LAT 4 HUM 4 LA" ' GODS
ClassNamesB(11) = "2de Graad" ' GODS
ClassNamesA(12) = "1A1 1A2"
ClassNamesB(12) = "1A"
ClassNamesA(13) = "1A1 1A2 2A KT 2A MT-W" ' MIS
ClassNamesB(13) = "" ' MIS; replace full cell with "MIS" so one line only
ClassNamesA(14) = "5 LAT-MT"
ClassNamesB(14) = "5 LAT"
ClassNamesA(15) = "1A1 6 LAT-MT 4 LAT 3 HU" ' MIS
ClassNamesB(15) = "" ' MIS; replace full cell with "MIS" so one line only
ClassNamesA(16) = "5 LAT-MT 6 LAT-MT"
ClassNamesB(16) = "5/6 LAT"
ClassReplace = Application.Union(ClassNamesA, ClassNamesB)
For i = 1 To UBound(ClassReplace)
Selection.Replace What:=ClassReplace(i, 1), Replacement:=ClassReplace(i, 2), _
LookAt:=xlPart, MatchCase:=True
Next i
errorcatch: MsgBox ("Error")
End SubNote: I have defined the range of the arrays as 1 To 20 (even though there are only 16 elements) to anticipate the possibility of adding more. If it's possible to make these arrays dynamic, all the better.
Challenges:
- Some of the elements in ClassNamesA are identical (ex. 9 and 11, 7 and 13), but the outputs are different. I have indicated in comment the affected course names. For example, use A(9) if the course name is LO or BEZ, but use A(11) if the course name is GODS. Use A(13) if the course name is MIS, but use A(7) in all other cases.
- For indexes 13 and 15, the full cell should be replaced by MIS. This is an all-school activity, so it is not necessary to display the class names. I have set the value of the index in B to "", but there should not be a second line in these cells.
VBA Code:
ClassReplace = Application.Union(ClassNamesA, ClassNamesB)Thank you in advance for your help. My apologies in advance for not being able to respond again today--I'm leaving now for my godson's birthday party--but I will respond first thing tomorrow. Wishing you all a happy, safe, and healthy new year!
