Userform Variables and advice

M

markswjh

New Member
Joined
Aug 31, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows
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

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
 

Attachments

  • MrXlGrBound.PNG
    MrXlGrBound.PNG
    49.9 KB · Views: 15
  • MrXlUsrFrm.PNG
    MrXlUsrFrm.PNG
    11.1 KB · Views: 14

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
You must log in or register to reply here.

Similar threads

K
  • Solved
formating question
Replies
3
Views
93
Micron
M
P
  • Question
Issue with button not running in spread sheet only in test mode
Replies
1
Views
206
6StringJazzer
6StringJazzer
L
  • Locked
  • Question
Help Cleaning Up Code
Replies
2
Views
352
RoryA
RoryA
K
  • Solved
create a button that do actions
2 3
Replies
22
Views
885
kerm007
K
J
  • Question
VBA Code to Add Rows is slowing down
Replies
5
Views
533
JayB0730
J

Forum statistics

Threads
1,215,429
Messages
6,124,843
Members
449,193
Latest member
MikeVol

Share this page

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