Hello all
I am making a userform to fill in grade boundaries automatically.
I have attached a copy of what I have so far - before you say it, I'm a novice in VBA and coding, so I am aware there is a lot of duplicated code and probably badly written bits.
However. In the userform you enter the grade boundaries and the code then inputs these with the Overall grade, the percentage and the boundary. So far I have been able to get it to enter the overall grade and percentage, but what I want it to do next is to identify the number that an 1 should equal, and fill in 2 until the number that a 3 should equal and then fill in 3 until the number that a 4 should equal and so on and so fourth. I'm just not entirely sure how to do this.
The image below gives you an idea of what I want it to look like.
The Code below shows you what I have so far - it's messy I know but please know I'm a novice!
Will
I am making a userform to fill in grade boundaries automatically.
I have attached a copy of what I have so far - before you say it, I'm a novice in VBA and coding, so I am aware there is a lot of duplicated code and probably badly written bits.
However. In the userform you enter the grade boundaries and the code then inputs these with the Overall grade, the percentage and the boundary. So far I have been able to get it to enter the overall grade and percentage, but what I want it to do next is to identify the number that an 1 should equal, and fill in 2 until the number that a 3 should equal and then fill in 3 until the number that a 4 should equal and so on and so fourth. I'm just not entirely sure how to do this.
The image below gives you an idea of what I want it to look like.
The Code below shows you what I have so far - it's messy I know but please know I'm a novice!
Will
VBA Code:
Private Sub SubmitButton_Click()
Dim YearInp As String
Dim OVFSTAR As Integer
Dim OVFA As Integer
Dim OVFB As Integer
Dim OVFC As Integer
Dim OVFD As Integer
Dim OVFE As Integer
Dim OVHSTAR As Integer
Dim OVHA As Integer
Dim OVHB As Integer
Dim OVHC As Integer
Dim OVHD As Integer
Dim OVHE As Integer
Dim LIFSTAR As Integer
Dim LIFA As Integer
Dim LIFB As Integer
Dim LIFC As Integer
Dim LIFD As Integer
Dim LIFE As Integer
Dim LIHSTAR As Integer
Dim LIHA As Integer
Dim LIHB As Integer
Dim LIHC As Integer
Dim LIHD As Integer
Dim LIHE As Integer
Dim REFSTAR As Integer
Dim REFA As Integer
Dim REFB As Integer
Dim REFC As Integer
Dim REFD As Integer
Dim REFE As Integer
Dim REHSTAR As Integer
Dim REHA As Integer
Dim REHB As Integer
Dim REHC As Integer
Dim REHD As Integer
Dim REHE As Integer
Dim WRFSTAR As Integer
Dim WRFA As Integer
Dim WRFB As Integer
Dim WRFC As Integer
Dim WRFD As Integer
Dim WRFE As Integer
Dim WRHSTAR As Integer
Dim WRHA As Integer
Dim WRHB As Integer
Dim WRHC As Integer
Dim WRHD As Integer
Dim WRHE As Integer
Dim SPFSTAR As Integer
Dim SPFA As Integer
Dim SPFB As Integer
Dim SPFC As Integer
Dim SPFD As Integer
Dim SPFE As Integer
Dim SPHSTAR As Integer
Dim SPHA As Integer
Dim SPHB As Integer
Dim SPHC As Integer
Dim SPHD As Integer
Dim SPHE As Integer
Dim TitleBar As String
Dim TotF As Integer
Dim TotH As Integer
Dim ListTotF As Integer
Dim ListTotH As Integer
Dim ReTotF As Integer
Dim ReTotH As Integer
Dim WrTotF As Integer
Dim WrTotH As Integer
Dim SpTotF As Integer
Dim SpTotH As Integer
Dim iLastRow As Integer
Dim sht1 As Worksheet
Dim LastVal As Integer
Dim CurrentMark As Integer
Dim TopGrade As Integer
'Set sht1 = Sheets("Sheet1")
'iLastRow = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row + 1
'sht1.Cells(iLastRow, 1).Value = YearInputBox.Value
'sht1.Cells(iLastRow, 2).Value = OVFSTARINP.Value
'Unload UserForm1
'On Error Resume Next
YearInp = YearInputBox.Value
Debug.Print YearInp
Debug.Print ListOverallF
Debug.Print ListOverallH
Let OVFSTAR = OVFSTARINP.Value
OVFA = OVFAINP
OVFB = OVFBINP
OVFC = OVFCINP
OVFD = OVFDINP
OVFE = OVFEINP
Let OVHSTAR = OVHSTARINP.Value
OVHA = OVHAINP
OVHB = OVHBINP
OVHC = OVHCINP
OVHD = OVHDINP
OVHE = OVHEINP
Let LIFSTAR = LIFSTARINP.Value
LIFA = LIFAINP
LIFB = LIFBINP
LIFC = LIFCINP
LIFD = LIFDINP
LIFE = LIFEINP
Let LIHSTAR = LIHSTARINP.Value
LIHA = LIHAINP
LIHB = LIHBINP
LIHC = LIHCINP
LIHD = LIHDINP
LIHE = LIHEINP
Let REFSTAR = REFSTARINP.Value
REFA = REFAINP
REFB = REFBINP
REFC = REFCINP
REFD = REFDINP
REFE = REFEINP
Let REHSTAR = REHSTARINP.Value
REHA = REHAINP
REHB = REHBINP
REHC = REHCINP
REHD = REHDINP
REHE = REHEINP
Let WRFSTAR = WRFSTARINP.Value
WRFA = WRFAINP
WRFB = WRFBINP
WRFC = WRFCINP
WRFD = WRFDINP
WRFE = WRFEINP
Let WRHSTAR = WRHSTARINP.Value
WRHA = WRHAINP
WRHB = WRHBINP
WRHC = WRHCINP
WRHD = WRHDINP
WRHE = WRHEINP
Let SPFSTAR = SPFSTARINP.Value
SPFA = SPFAINP
SPFB = SPFBINP
SPFC = SPFCINP
SPFD = SPFDINP
SPFE = SPFEINP
Let SPHSTAR = SPHSTARINP.Value
SPHA = SPHAINP
SPHB = SPHBINP
SPHC = SPHCINP
SPHD = SPHDINP
SPHE = SPHEINP
'-----------------------------------LISTENING-------------------------------------------
Range("A1:F1").Select
Selection.Merge
Selection.Value = "Listening " & YearInp & " Boundaries"
Selection.HorizontalAlignment = xlCenter
Selection.Interior.Color = 5296274
Range("A2:C2").Select
Selection.HorizontalAlignment = xlCenter
Selection.Merge
ActiveCell.Value = "Higher"
Range("D2:F2").Select
Selection.HorizontalAlignment = xlCenter
Selection.Merge
ActiveCell.Value = "Foundation"
Range("A3").Select
ActiveCell.Value = "Mark"
Range("B3").Select
ActiveCell.Value = "%"
Range("C3").Select
ActiveCell.Value = "Grade"
Range("D3").Select
ActiveCell.Value = "Mark"
Range("E3").Select
ActiveCell.Value = "%"
Range("F3").Select
ActiveCell.Value = "Grade"
Range("A1:F3").Select
Selection.Borders(xlEdgeLeft).Weight = xlThin
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeBottom).Weight = xlThin
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlInsideVertical).Weight = xlThin
Selection.Borders(xlInsideHorizontal).Weight = xlThin
Range("A1:F1").Select
Selection.Borders(xlEdgeLeft).Weight = xlMedium
Selection.Borders(xlEdgeTop).Weight = xlMedium
Selection.Borders(xlEdgeBottom).Weight = xlMedium
Selection.Borders(xlEdgeRight).Weight = xlMedium
Range("A2:F2").Select
Selection.Borders(xlEdgeLeft).Weight = xlMedium
Selection.Borders(xlEdgeTop).Weight = xlMedium
Selection.Borders(xlEdgeBottom).Weight = xlMedium
Selection.Borders(xlEdgeRight).Weight = xlMedium
Range("A3:F3").Select
Selection.Borders(xlEdgeLeft).Weight = xlMedium
Selection.Borders(xlEdgeTop).Weight = xlMedium
Selection.Borders(xlEdgeBottom).Weight = xlMedium
Selection.Borders(xlEdgeRight).Weight = xlMedium
Columns("B:B").NumberFormat = "0%"
Columns("E:E").NumberFormat = "0%"
Range("A3:F3").Select
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
'--------------------------------- ADD Mark Numbers ------------------------
Let TotF = ListOverallF.Value
Let TotH = ListOverallH.Value
LastVal = 0
Set sht1 = Sheets("Sheet1")
iLastRow = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row + 1
ActiveCell.Offset(1, 0).Select
Do While LastVal <= TotF
iLastRow = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row + 1
ActiveCell.Value = LastVal
LastVal = LastVal + 1
ActiveCell.Offset(1, 0).Select
Loop
'----------------------ADD PERCENTAGES ---------------------------------
ActiveCell.Offset(-1, 0).Select
TopGrade = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Do While LastVal < TopGrade
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Loop
'--------------------ADD SECOND MARK NUMBERS--------------------------
ActiveCell.Offset(0, 2).Select
Selection.End(xlUp).Select
LastVal = 0
ActiveCell.Offset(1, 0).Select
Do While LastVal <= TotH
iLastRow = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row + 2
ActiveCell.Value = LastVal
LastVal = LastVal + 1
ActiveCell.Offset(1, 0).Select
Loop
'----------------------ADD SECOND PERCENTAGES ---------------------------------
ActiveCell.Offset(-1, 0).Select
TopGrade = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Do While LastVal < TopGrade
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Loop
'----------------------------READING--------------------------------------
Range("G1:L1").Select
Selection.Merge
Selection.Value = "Reading " & YearInp & " Boundaries"
Selection.HorizontalAlignment = xlCenter
Selection.Interior.Color = 5296274
Range("G2:I2").Select
Selection.HorizontalAlignment = xlCenter
Selection.Merge
ActiveCell.Value = "Higher"
Range("J2:L2").Select
Selection.HorizontalAlignment = xlCenter
Selection.Merge
ActiveCell.Value = "Foundation"
Range("G3").Select
ActiveCell.Value = "Mark"
Range("H3").Select
ActiveCell.Value = "%"
Range("I3").Select
ActiveCell.Value = "Grade"
Range("J3").Select
ActiveCell.Value = "Mark"
Range("K3").Select
ActiveCell.Value = "%"
Range("L3").Select
ActiveCell.Value = "Grade"
Range("G1:L3").Select
Selection.Borders(xlEdgeLeft).Weight = xlThin
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeBottom).Weight = xlThin
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlInsideVertical).Weight = xlThin
Selection.Borders(xlInsideHorizontal).Weight = xlThin
Range("G1:L1").Select
Selection.Borders(xlEdgeLeft).Weight = xlMedium
Selection.Borders(xlEdgeTop).Weight = xlMedium
Selection.Borders(xlEdgeBottom).Weight = xlMedium
Selection.Borders(xlEdgeRight).Weight = xlMedium
Range("G2:L2").Select
Selection.Borders(xlEdgeLeft).Weight = xlMedium
Selection.Borders(xlEdgeTop).Weight = xlMedium
Selection.Borders(xlEdgeBottom).Weight = xlMedium
Selection.Borders(xlEdgeRight).Weight = xlMedium
Range("G3:L3").Select
Selection.Borders(xlEdgeLeft).Weight = xlMedium
Selection.Borders(xlEdgeTop).Weight = xlMedium
Selection.Borders(xlEdgeBottom).Weight = xlMedium
Selection.Borders(xlEdgeRight).Weight = xlMedium
Columns("H:H").NumberFormat = "0%"
Columns("K:K").NumberFormat = "0%"
Range("G3:L3").Select
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Let TotF = ReadTotF.Value
Let TotH = ReadTotH.Value
LastVal = 0
Set sht1 = Sheets("Sheet1")
iLastRow = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row + 1
ActiveCell.Offset(1, 0).Select
Do While LastVal <= TotF
iLastRow = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row + 1
ActiveCell.Value = LastVal
LastVal = LastVal + 1
ActiveCell.Offset(1, 0).Select
Loop
'----------------------ADD PERCENTAGES ---------------------------------
ActiveCell.Offset(-1, 0).Select
TopGrade = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Do While LastVal < TopGrade
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Loop
'--------------------ADD SECOND MARK NUMBERS--------------------------
ActiveCell.Offset(0, 2).Select
Selection.End(xlUp).Select
LastVal = 0
ActiveCell.Offset(1, 0).Select
Do While LastVal <= TotH
iLastRow = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row + 2
ActiveCell.Value = LastVal
LastVal = LastVal + 1
ActiveCell.Offset(1, 0).Select
Loop
'----------------------ADD SECOND PERCENTAGES ---------------------------------
ActiveCell.Offset(-1, 0).Select
TopGrade = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Do While LastVal < TopGrade
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Loop
'----------------------------WRITING --------------------------------------
Range("M1:R1").Select
Selection.Merge
Selection.Value = "Writing " & YearInp & " Boundaries"
Selection.HorizontalAlignment = xlCenter
Selection.Interior.Color = 5296274
Range("M2:O2").Select
Selection.HorizontalAlignment = xlCenter
Selection.Merge
ActiveCell.Value = "Higher"
Range("P2:R2").Select
Selection.HorizontalAlignment = xlCenter
Selection.Merge
ActiveCell.Value = "Foundation"
Range("M3").Select
ActiveCell.Value = "Mark"
Range("N3").Select
ActiveCell.Value = "%"
Range("O3").Select
ActiveCell.Value = "Grade"
Range("P3").Select
ActiveCell.Value = "Mark"
Range("Q3").Select
ActiveCell.Value = "%"
Range("R3").Select
ActiveCell.Value = "Grade"
Range("M1:R3").Select
Selection.Borders(xlEdgeLeft).Weight = xlThin
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeBottom).Weight = xlThin
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlInsideVertical).Weight = xlThin
Selection.Borders(xlInsideHorizontal).Weight = xlThin
Range("M1:R1").Select
Selection.Borders(xlEdgeLeft).Weight = xlMedium
Selection.Borders(xlEdgeTop).Weight = xlMedium
Selection.Borders(xlEdgeBottom).Weight = xlMedium
Selection.Borders(xlEdgeRight).Weight = xlMedium
Range("M2:R2").Select
Selection.Borders(xlEdgeLeft).Weight = xlMedium
Selection.Borders(xlEdgeTop).Weight = xlMedium
Selection.Borders(xlEdgeBottom).Weight = xlMedium
Selection.Borders(xlEdgeRight).Weight = xlMedium
Range("M3:R3").Select
Selection.Borders(xlEdgeLeft).Weight = xlMedium
Selection.Borders(xlEdgeTop).Weight = xlMedium
Selection.Borders(xlEdgeBottom).Weight = xlMedium
Selection.Borders(xlEdgeRight).Weight = xlMedium
Columns("N:N").NumberFormat = "0%"
Columns("Q:Q").NumberFormat = "0%"
Range("M3:R3").Select
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Let TotF = WriteTotF.Value
Let TotH = WriteTotH.Value
LastVal = 0
Set sht1 = Sheets("Sheet1")
iLastRow = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row + 1
ActiveCell.Offset(1, 0).Select
Do While LastVal <= TotF
iLastRow = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row + 1
ActiveCell.Value = LastVal
LastVal = LastVal + 1
ActiveCell.Offset(1, 0).Select
Loop
'----------------------ADD PERCENTAGES ---------------------------------
ActiveCell.Offset(-1, 0).Select
TopGrade = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Do While LastVal < TopGrade
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Loop
ActiveCell.Offset(0, 2).Select
Selection.End(xlUp).Select
LastVal = 0
ActiveCell.Offset(1, 0).Select
Do While LastVal <= TotH
iLastRow = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row + 2
ActiveCell.Value = LastVal
LastVal = LastVal + 1
ActiveCell.Offset(1, 0).Select
Loop
'----------------------ADD PERCENTAGES ---------------------------------
ActiveCell.Offset(-1, 0).Select
TopGrade = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Do While LastVal < TopGrade
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Loop
'----------------------------SPEAKING --------------------------------------
Range("S1:X1").Select
Selection.Merge
Selection.Value = "Speaking " & YearInp & " Boundaries"
Selection.HorizontalAlignment = xlCenter
Selection.Interior.Color = 5296274
Range("S2:U2").Select
Selection.HorizontalAlignment = xlCenter
Selection.Merge
ActiveCell.Value = "Higher"
Range("V2:X2").Select
Selection.HorizontalAlignment = xlCenter
Selection.Merge
ActiveCell.Value = "Foundation"
Range("S3").Select
ActiveCell.Value = "Mark"
Range("T3").Select
ActiveCell.Value = "%"
Range("U3").Select
ActiveCell.Value = "Grade"
Range("V3").Select
ActiveCell.Value = "Mark"
Range("W3").Select
ActiveCell.Value = "%"
Range("X3").Select
ActiveCell.Value = "Grade"
Range("S1:X3").Select
Selection.Borders(xlEdgeLeft).Weight = xlThin
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeBottom).Weight = xlThin
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlInsideVertical).Weight = xlThin
Selection.Borders(xlInsideHorizontal).Weight = xlThin
Range("S1:X1").Select
Selection.Borders(xlEdgeLeft).Weight = xlMedium
Selection.Borders(xlEdgeTop).Weight = xlMedium
Selection.Borders(xlEdgeBottom).Weight = xlMedium
Selection.Borders(xlEdgeRight).Weight = xlMedium
Range("S2:X2").Select
Selection.Borders(xlEdgeLeft).Weight = xlMedium
Selection.Borders(xlEdgeTop).Weight = xlMedium
Selection.Borders(xlEdgeBottom).Weight = xlMedium
Selection.Borders(xlEdgeRight).Weight = xlMedium
Range("S3:X3").Select
Selection.Borders(xlEdgeLeft).Weight = xlMedium
Selection.Borders(xlEdgeTop).Weight = xlMedium
Selection.Borders(xlEdgeBottom).Weight = xlMedium
Selection.Borders(xlEdgeRight).Weight = xlMedium
Columns("T:T").NumberFormat = "0%"
Columns("W:W").NumberFormat = "0%"
Range("S3:X3").Select
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Let TotF = SpeakTotF.Value
Let TotH = SpeakTotH.Value
LastVal = 0
Set sht1 = Sheets("Sheet1")
iLastRow = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row + 1
ActiveCell.Offset(1, 0).Select
Do While LastVal <= TotF
iLastRow = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row + 1
ActiveCell.Value = LastVal
LastVal = LastVal + 1
ActiveCell.Offset(1, 0).Select
Loop
'----------------------ADD PERCENTAGES ---------------------------------
ActiveCell.Offset(-1, 0).Select
TopGrade = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Do While LastVal < TopGrade
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Loop
ActiveCell.Offset(0, 2).Select
Selection.End(xlUp).Select
LastVal = 0
ActiveCell.Offset(1, 0).Select
Do While LastVal <= TotH
iLastRow = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row + 2
ActiveCell.Value = LastVal
LastVal = LastVal + 1
ActiveCell.Offset(1, 0).Select
Loop
'----------------------ADD PERCENTAGES ---------------------------------
ActiveCell.Offset(-1, 0).Select
TopGrade = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Do While LastVal < TopGrade
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Loop
'----------------------------OVERALL --------------------------------------
Range("Y1:AD1").Select
Selection.Merge
Selection.Value = "Overall " & YearInp & " Boundaries"
Selection.HorizontalAlignment = xlCenter
Selection.Interior.Color = 5296274
Range("Y2:AA2").Select
Selection.HorizontalAlignment = xlCenter
Selection.Merge
ActiveCell.Value = "Higher"
Range("AB2:AD2").Select
Selection.HorizontalAlignment = xlCenter
Selection.Merge
ActiveCell.Value = "Foundation"
Range("Y3").Select
ActiveCell.Value = "Mark"
Range("Z3").Select
ActiveCell.Value = "%"
Range("AA3").Select
ActiveCell.Value = "Grade"
Range("AB3").Select
ActiveCell.Value = "Mark"
Range("AC3").Select
ActiveCell.Value = "%"
Range("AD3").Select
ActiveCell.Value = "Grade"
Range("Y1:AD3").Select
Selection.Borders(xlEdgeLeft).Weight = xlThin
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeBottom).Weight = xlThin
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlInsideVertical).Weight = xlThin
Selection.Borders(xlInsideHorizontal).Weight = xlThin
Range("Y1:AD1").Select
Selection.Borders(xlEdgeLeft).Weight = xlMedium
Selection.Borders(xlEdgeTop).Weight = xlMedium
Selection.Borders(xlEdgeBottom).Weight = xlMedium
Selection.Borders(xlEdgeRight).Weight = xlMedium
Range("Y2:AD2").Select
Selection.Borders(xlEdgeLeft).Weight = xlMedium
Selection.Borders(xlEdgeTop).Weight = xlMedium
Selection.Borders(xlEdgeBottom).Weight = xlMedium
Selection.Borders(xlEdgeRight).Weight = xlMedium
Range("Y3:AD3").Select
Selection.Borders(xlEdgeLeft).Weight = xlMedium
Selection.Borders(xlEdgeTop).Weight = xlMedium
Selection.Borders(xlEdgeBottom).Weight = xlMedium
Selection.Borders(xlEdgeRight).Weight = xlMedium
Columns("Z:Z").NumberFormat = "0%"
Columns("AC:AC").NumberFormat = "0%"
Range("Y3:AD3").Select
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Let TotF = OverallF.Value
Let TotH = OverallH.Value
LastVal = 0
Set sht1 = Sheets("Sheet1")
iLastRow = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row + 1
ActiveCell.Offset(1, 0).Select
Do While LastVal <= TotF
iLastRow = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row + 1
ActiveCell.Value = LastVal
LastVal = LastVal + 1
ActiveCell.Offset(1, 0).Select
Loop
'----------------------ADD PERCENTAGES ---------------------------------
ActiveCell.Offset(-1, 0).Select
TopGrade = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Do While LastVal < TopGrade
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Loop
ActiveCell.Offset(0, 2).Select
Selection.End(xlUp).Select
LastVal = 0
ActiveCell.Offset(1, 0).Select
Do While LastVal <= TotH
iLastRow = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row + 2
ActiveCell.Value = LastVal
LastVal = LastVal + 1
ActiveCell.Offset(1, 0).Select
Loop
'----------------------ADD PERCENTAGES ---------------------------------
ActiveCell.Offset(-1, 0).Select
TopGrade = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Do While LastVal < TopGrade
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
CurrentMark = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Percentage = CurrentMark / TopGrade
ActiveCell.Value = Percentage
LastVal = CurrentMark
Loop
Unload Me
End Sub