Excel - command button functionality

If you want to achieve this by just. Formula then pls let me know. I will help you our on this.

Thanks

Hi andymacf,

I have 2 questions:

1)
The Tables when you get them have data till
For SBA Angoff Row 95
For EMI Angoff Row 128

and in the file attached you added manually the totals and the rest of items ?

2) If my assumption of 1) is correct then you need the macro to do all the rest ?
ie Total under each Column then the totals in D100, D104, D108 with their description
Right ?

Pls advise
gowflow

Hi gowflow

The count of rows will differ for each input.

However, the max rows in SBA Angoff will be 110, and the max rows in EMI Angoff will be 135.

Yes, I added the totals to show the calculations that are needed.

If I am being honest, I am looking for a similar solution to the survey import/output/graphs.  I was leaving the other worksheets for a future post.

Thanks
Andy

also an other one at the extreme right you have Total Angoff in one file you had a formula already in the second no formula for that column. Do you want the macro to put a formula in that column ?

gowflow

Yes please, there should be a total angoff column in both SBA & EMI sheets

So originally the Total Columns is there or you also added it here manually ?
gowflow

No, I added these manually too, because the number of columns change each time too depending upon how many exmainers attend the event.  So the formulae need to take this changing column count into consideration.

Andy

Last before I post

the figures are sometimes with 4 to 7 decimals, shall we cut decimals at 2 ?? or leave to whatever it is

Like I have figures like
5.25
5.75
5.888297872

and
7.156976421
7.814067063
8.871926292

and
97.89361702            
6.118351064            

gowflow

Hi still did not receive your reply on last comment. I took the liberty to format at 2 decimals however if you do not want that we can easily remove that.

Here is the code that is lying in a module

Sub GetTotals(WS As Worksheet)
Dim MaxRow As Long, MaxCol As Long, I As Long
Dim sMaxCol As String

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'---> Set Variables
MaxRow = WS.Range("B" & WS.Rows.Count).End(xlUp).Row
MaxCol = WS.Columns(WS.Columns.Count).End(xlToLeft).Column
sMaxCol = Chr(MaxCol - 1 + 65)

'---> Clean Existing Data beyond MaxRow
WS.Range(MaxRow + 1 & ":" & WS.Rows.Count).EntireRow.Delete
If WS.Cells(1, MaxCol) = "Total Angoff" Then
    WS.Columns(MaxCol).EntireColumn.Delete
    MaxCol = WS.Columns(WS.Columns.Count).End(xlToLeft).Column
    sMaxCol = Chr(MaxCol - 1 + 65)
Else
    WS.Columns(MaxCol + 1).EntireColumn.Delete
End If

'---> Insert Labels
WS.Cells(1, MaxCol + 1) = "Total Angoff"
WS.Columns(MaxCol).EntireColumn.Copy
WS.Cells(1, MaxCol + 1).PasteSpecial Paste:=xlPasteFormats
WS.Cells(MaxRow + 1, "A") = "Average per examiner"
WS.Range("A" & MaxRow + 1 & ":B" & MaxRow + 1).Merge
WS.Cells(MaxRow + 5, "A") = "Total of all examiner's average marks"
WS.Range("A" & MaxRow + 5 & ":B" & MaxRow + 5).Merge
WS.Cells(MaxRow + 9, "A") = "Average per examiner = pass mark"
WS.Range("A" & MaxRow + 9 & ":B" & MaxRow + 9).Merge
WS.Cells(MaxRow + 13, "A") = "Pass mark as a percentage"
WS.Range("A" & MaxRow + 13 & ":B" & MaxRow + 13).Merge

'---> Insert Formulas
'Total Angoff
WS.Range(WS.Cells(2, MaxCol + 1), WS.Cells(MaxRow, MaxCol + 1)).Formula = "=SUM(D2:" & sMaxCol & "2)/COUNTA(D2:" & sMaxCol & "2)"
WS.Range(WS.Cells(2, MaxCol + 1), WS.Cells(MaxRow, MaxCol + 1)).NumberFormat = "#,###.00"

'Average per Examiner =SUM(D2:D95)/COUNTA(D2:D95)
WS.Range(WS.Range("D" & MaxRow + 1), WS.Cells(MaxRow + 1, MaxCol)).Formula = "=SUM(D2:D" & MaxRow & ")/COUNTA(D2:D" & MaxRow & ")"
WS.Range(WS.Range("D" & MaxRow + 1), WS.Cells(MaxRow + 1, MaxCol)).NumberFormat = "#,###.00"
WS.Range("A" & MaxRow + 1 & ":" & sMaxCol & MaxRow + 1).Borders.LineStyle = xlContinuous
WS.Range("A" & MaxRow + 1 & ":" & sMaxCol & MaxRow + 1).Borders.Weight = xlThin

'Total of all examiner's average marks =SUM(D96:S96)
WS.Range("D" & MaxRow + 5).Formula = "=SUM(D" & MaxRow + 1 & ":" & sMaxCol & MaxRow + 1 & ")"
WS.Range("D" & MaxRow + 5).NumberFormat = "#,###.00"
WS.Range("D" & MaxRow + 5).HorizontalAlignment = xlCenter
WS.Range("D" & MaxRow + 5 & ":F" & MaxRow + 5).Merge
WS.Range("D" & MaxRow + 5 & ":F" & MaxRow + 5).BorderAround LineStyle:=xlContinuous, Weight:=xlThin

'Average per examiner = pass mark =SUM(D100)/16
WS.Range("D" & MaxRow + 9).Formula = "=SUM(D" & MaxRow + 5 & ")/" & MaxCol - 3
WS.Range("D" & MaxRow + 9).NumberFormat = "#,###.00"
WS.Range("D" & MaxRow + 9).HorizontalAlignment = xlCenter
WS.Range("D" & MaxRow + 9 & ":F" & MaxRow + 9).Merge
WS.Range("D" & MaxRow + 9 & ":F" & MaxRow + 9).BorderAround LineStyle:=xlContinuous, Weight:=xlThin


'Pass mark as a percentage =SUM(D104)/10
WS.Range("D" & MaxRow + 13).Formula = "=SUM(D" & MaxRow + 9 & ")/10"
WS.Range("D" & MaxRow + 13).NumberFormat = "0.00%"
WS.Range("D" & MaxRow + 13).HorizontalAlignment = xlCenter
WS.Range("D" & MaxRow + 13 & ":F" & MaxRow + 13).Merge
WS.Range("D" & MaxRow + 13 & ":F" & MaxRow + 13).BorderAround LineStyle:=xlContinuous, Weight:=xlThin

WS.Range("A1").Select

'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

MsgBox ("Totals Inserted.")

End Sub

Select allOpen in new window

For easy Access I have created a button in each of the sheets 'SBA Angoff' & 'EMI Angoff' that is called Totals that will launch the macro for the said sheet.

Please test it, with different columns and different rows and let me know your comments.

gowflow
Stats-calculations-V01.xlsm

Thanks gowflow
Sorry I did not respond to you earlier comment, I had to leave work as I am feeling rather ill at the moment.  When I feel a bit better I will take a look at the sample you have posted.

Thanks again
Andy

ok Take care, but you didn't tell me 2 decimals is fine ?
gowflow

For the total Angoff column, 2 decimals is fine, but for the bottom row totals, they should remain as they are.  The calculated fields below the total row can display 2 dec places but must not be rounded to 2 dec places, this is very important for calculations later on.

Thanks
Andy

ok here it is use this version. Only Total Angoff is 2 decimal all the rest is as is. Percentage is 2 decimals.
gowflow
Stats-calculations-V02.xlsm

Hi gowflow

This looks good, with totals adjusting themselves if rows are removed.

So, when I do an import of the SBA and EMI sheets will the 'Totals' buttons remain, or how do I activate them?

Thanks
Andy

Well if you import a new sheet altogether, then for sure the buttons will not exist in the new sheet as not catered so, however if the sheet remains the same same you Import (ie replace the existing data with new one on these same sheets) then YES the buttons will obviously be there.

So how is your process ?
gowflow

Hi gowflow

That might be a problem, as the number of columns changes for every import.  Do you think it might be easier to have a separate sheet with command buttons for the totals calculations.  This would mean I could import my SBA & EMI sheets, then click the command button and this would insert the totals and calculate the pass mark.

Look forward to your views on this
Andy

Here it is: This is independent of the button. I cancelled the button just from the sheet that you want run this macro.

Sub GetTotals()
Dim WS As Worksheet
Dim MaxRow As Long, MaxCol As Long, I As Long
Dim sMaxCol As String

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'---> Set Variables
Set WS = ActiveSheet
MaxRow = WS.Range("B" & WS.Rows.Count).End(xlUp).Row
MaxCol = WS.Columns(WS.Columns.Count).End(xlToLeft).Column
sMaxCol = Chr(MaxCol - 1 + 65)

'---> Clean Existing Data beyond MaxRow
WS.Range(MaxRow + 1 & ":" & WS.Rows.Count).EntireRow.Delete
If WS.Cells(1, MaxCol) = "Total Angoff" Then
    WS.Columns(MaxCol).EntireColumn.Delete
    MaxCol = WS.Columns(WS.Columns.Count).End(xlToLeft).Column
    sMaxCol = Chr(MaxCol - 1 + 65)
Else
    WS.Columns(MaxCol + 1).EntireColumn.Delete
End If

'---> Insert Labels
WS.Cells(1, MaxCol + 1) = "Total Angoff"
WS.Columns(MaxCol).EntireColumn.Copy
WS.Cells(1, MaxCol + 1).PasteSpecial Paste:=xlPasteFormats
WS.Cells(MaxRow + 1, "A") = "Average per examiner"
WS.Range("A" & MaxRow + 1 & ":B" & MaxRow + 1).Merge
WS.Cells(MaxRow + 5, "A") = "Total of all examiner's average marks"
WS.Range("A" & MaxRow + 5 & ":B" & MaxRow + 5).Merge
WS.Cells(MaxRow + 9, "A") = "Average per examiner = pass mark"
WS.Range("A" & MaxRow + 9 & ":B" & MaxRow + 9).Merge
WS.Cells(MaxRow + 13, "A") = "Pass mark as a percentage"
WS.Range("A" & MaxRow + 13 & ":B" & MaxRow + 13).Merge

'---> Insert Formulas
'Total Angoff
WS.Range(WS.Cells(2, MaxCol + 1), WS.Cells(MaxRow, MaxCol + 1)).Formula = "=SUM(D2:" & sMaxCol & "2)/COUNTA(D2:" & sMaxCol & "2)"
WS.Range(WS.Cells(2, MaxCol + 1), WS.Cells(MaxRow, MaxCol + 1)).NumberFormat = "#,###.00"

'Average per Examiner =SUM(D2:D95)/COUNTA(D2:D95)
WS.Range(WS.Range("D" & MaxRow + 1), WS.Cells(MaxRow + 1, MaxCol)).Formula = "=SUM(D2:D" & MaxRow & ")/COUNTA(D2:D" & MaxRow & ")"
'WS.Range(WS.Range("D" & MaxRow + 1), WS.Cells(MaxRow + 1, MaxCol)).NumberFormat = "#,###.00"
WS.Range("A" & MaxRow + 1 & ":" & sMaxCol & MaxRow + 1).Borders.LineStyle = xlContinuous
WS.Range("A" & MaxRow + 1 & ":" & sMaxCol & MaxRow + 1).Borders.Weight = xlThin

'Total of all examiner's average marks =SUM(D96:S96)
WS.Range("D" & MaxRow + 5).Formula = "=SUM(D" & MaxRow + 1 & ":" & sMaxCol & MaxRow + 1 & ")"
'WS.Range("D" & MaxRow + 5).NumberFormat = "#,###.00"
WS.Range("D" & MaxRow + 5).HorizontalAlignment = xlCenter
WS.Range("D" & MaxRow + 5 & ":F" & MaxRow + 5).Merge
WS.Range("D" & MaxRow + 5 & ":F" & MaxRow + 5).BorderAround LineStyle:=xlContinuous, Weight:=xlThin

'Average per examiner = pass mark =SUM(D100)/16
WS.Range("D" & MaxRow + 9).Formula = "=SUM(D" & MaxRow + 5 & ")/" & MaxCol - 3
'WS.Range("D" & MaxRow + 9).NumberFormat = "#,###.00"
WS.Range("D" & MaxRow + 9).HorizontalAlignment = xlCenter
WS.Range("D" & MaxRow + 9 & ":F" & MaxRow + 9).Merge
WS.Range("D" & MaxRow + 9 & ":F" & MaxRow + 9).BorderAround LineStyle:=xlContinuous, Weight:=xlThin


'Pass mark as a percentage =SUM(D104)/10
WS.Range("D" & MaxRow + 13).Formula = "=SUM(D" & MaxRow + 9 & ")/10"
WS.Range("D" & MaxRow + 13).NumberFormat = "0.00%"
WS.Range("D" & MaxRow + 13).HorizontalAlignment = xlCenter
WS.Range("D" & MaxRow + 13 & ":F" & MaxRow + 13).Merge
WS.Range("D" & MaxRow + 13 & ":F" & MaxRow + 13).BorderAround LineStyle:=xlContinuous, Weight:=xlThin

WS.Range("A1").Select

'---> Ensable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

MsgBox ("Totals Inserted.")

End Sub

Select allOpen in new window

check the file.
gowflow
Stats-calculations-V03.xlsm

Hi gowflow

This may be the subject of another question but I have added a main sheet into the sample workbook (Attached), and was wondering if it would be possible to use buttons like this to facilitate the calculations.

The final part of the exercise would be to take the values from the 2 percentage boxes and feed them to the MASTER sheet. (Definitely a new question)

Your thoughts please

Thanks
Andy
Stats-calculations-V04.xlsm

Hi gowflow

Message understood, and apologies for not following protocol.  I was more than happy to start a new question for the 'Main' sheet as I realised it was outwith the scope of the original question.

I am very grateful for you doing it under this question.

Andy

An excellent solution to my issue, thanks gowflow, your efforts are greatly appreciated.

Andy

I am glad to know that the issue that  I feared has no foundation with you. Tks for the grade and feel free to post a link in here for the follow-up.
gowflow

Hi gowflow

A further question as a follow-up to this question.

Thanks Andy