Creating Clustered Column Chart in Macro with Variable Cell Ranges

G

gilbertg

New Member
Joined
Jan 9, 2014
Messages
1
I am trying to write a macro that will allow users to input a last name and three scores for a number of people. Median class (as in school classrrom) values (and interquartile ranges) are calculated. The user is then prompted for the historical values for each trial and the final test. Then I would like to prompt the user to specify the range of cells corresponding to one student (or participant) to be plotted against his or her classmates and the historical course values. I believe I have accomplished all of this except for the plotting portion. After searching the internet for 3 days I cannot find a suitable example of how I need to specify the variables for plotting. (I do know I should have an "Options Explicit" statement and variables should be named better, etc but wanted to get the code correct first. Below is an example of data. TIA for your help!

IDParticipantTrial 1Trial 2Trial 3Test
1Harle891010
2Gilbert4444
3Cochran6789
44444
55555
66666
77777
88888
98888
109999
1110101010
121010101010
Class Median7.57.588
Class IQR2.53.253.53.5
Course Median58910
Course IQR4.53.12.70.9

<tbody>
</tbody>



================= CUT HERE FOR CODE ==================


Public Sub Graph1()
Range("B2").Select
ActiveCell.Value = InputBox("Enter Participant 1 Name")
Range("C2").Select
ActiveCell.Value = InputBox("Enter Participant 1, Trial 1 Score")
Range("D2").Select
ActiveCell.Value = InputBox("Enter Participant 1, Trial 2 Score")
Range("E2").Select
ActiveCell.Value = InputBox("Enter Participant 1, Trial 3 Score")
Range("F2").Select
ActiveCell.Value = InputBox("Enter Participant 1, Test Score")

Range("B3").Select
ActiveCell.Value = InputBox("Enter Participant 2 Name")
Range("C3").Select
ActiveCell.Value = InputBox("Enter Participant 2 Trial 1 Score")
Range("D3").Select
ActiveCell.Value = InputBox("Enter Participant 2, Trial 2 Score")
Range("E3").Select
ActiveCell.Value = InputBox("Enter Participant 2, Trial 3 Score")
Range("F3").Select
ActiveCell.Value = InputBox("Enter Participant 2, Test Score")

Range("B4").Select
ActiveCell.Value = InputBox("Enter Participant 3 Name")
Range("C4").Select
ActiveCell.Value = InputBox("Enter Participant 3, Trial 1 Score")
Range("D4").Select
ActiveCell.Value = InputBox("Enter Participant 3, Trial 2 Score")
Range("E4").Select
ActiveCell.Value = InputBox("Enter Participant 3, Trial 3 Score")
Range("F4").Select
ActiveCell.Value = InputBox("Enter Participant 3, Test Score")

'Range("B5").Select
'ActiveCell.Value = InputBox("Enter Participant 4 Name")
'Range("C5").Select
'ActiveCell.Value = InputBox("Enter Participant 4, Trial 1 Score")
'Range("D5").Select
'ActiveCell.Value = InputBox("Enter Participant 4, Trial 2 Score")
'Range("E5").Select
'ActiveCell.Value = InputBox("Enter Participant 4, Trial 3 Score")
'Range("F5").Select
'ActiveCell.Value = InputBox("Enter Participant 4, Test Score")

'Range("B6").Select
'ActiveCell.Value = InputBox("Enter Participant 5 Name")
'Range("C6").Select
'ActiveCell.Value = InputBox("Enter Participant 5, Trial 1 Score")
'Range("D6").Select
'ActiveCell.Value = InputBox("Enter Participant 5, Trial 2 Score")
'Range("E6").Select
'ActiveCell.Value = InputBox("Enter Participant 5, Trial 3 Score")
'Range("F6").Select
'ActiveCell.Value = InputBox("Enter Participant 5, Test Score")

'Range("B7").Select
'ActiveCell.Value = InputBox("Enter Participant 6 Name")
'Range("C7").Select
'ActiveCell.Value = InputBox("Enter Participant 6, Trial 1 Score")
'Range("D7").Select
'ActiveCell.Value = InputBox("Enter Participant 6, Trial 2 Score")
'Range("E7").Select
'ActiveCell.Value = InputBox("Enter Participant 6, Trial 3 Score")
'Range("F7").Select
'ActiveCell.Value = InputBox("Enter Participant 6, Test Score")

'Range("B8").Select
'ActiveCell.Value = InputBox("Enter Participant 7 Name")
'Range("C8").Select
'ActiveCell.Value = InputBox("Enter Participant 7, Trial 1 Score")
'Range("D8").Select
'ActiveCell.Value = InputBox("Enter Participant 7, Trial 2 Score")
'Range("E8").Select
'ActiveCell.Value = InputBox("Enter Participant 7, Trial 3 Score")
'Range("F8").Select
'ActiveCell.Value = InputBox("Enter Participant 7, Test Score")

'Range("B9").Select
'ActiveCell.Value = InputBox("Enter Participant 8 Name")
'Range("C9").Select
'ActiveCell.Value = InputBox("Enter Participant 8, Trial 1 Score")
'Range("D9").Select
'ActiveCell.Value = InputBox("Enter Participant 8, Trial 2 Score")
'Range("E9").Select
'ActiveCell.Value = InputBox("Enter Participant 8, Trial 3 Score")
'Range("F9").Select
'ActiveCell.Value = InputBox("Enter Participant 8, Test Score")

'Range("B10").Select
'ActiveCell.Value = InputBox("Enter Participant 9 Name")
'Range("C10").Select
'ActiveCell.Value = InputBox("Enter Participant 9, Trial 1 Score")
'Range("D10").Select
'ActiveCell.Value = InputBox("Enter Participant 9, Trial 2 Score")
'Range("E10").Select
'ActiveCell.Value = InputBox("Enter Participant 9, Trial 3 Score")
'Range("F10").Select
'ActiveCell.Value = InputBox("Enter Participant 9, Test Score")

'Range("B11").Select
'ActiveCell.Value = InputBox("Enter Participant 10 Name")
'Range("C11").Select
'ActiveCell.Value = InputBox("Enter Participant 10, Trial 1 Score")
'Range("D11").Select
'ActiveCell.Value = InputBox("Enter Participant 10, Trial 2 Score")
'Range("E11").Select
'ActiveCell.Value = InputBox("Enter Participant 10, Trial 3 Score")
'Range("F11").Select
'ActiveCell.Value = InputBox("Enter Participant 10, Test Score")

'Range("B12").Select
'ActiveCell.Value = InputBox("Enter Participant 11 Name")
'Range("C12").Select
'ActiveCell.Value = InputBox("Enter Participant 11, Trial 1 Score")
'Range("D12").Select
'ActiveCell.Value = InputBox("Enter Participant 11, Trial 2 Score")
'Range("E12").Select
'ActiveCell.Value = InputBox("Enter Participant 11, Trial 3 Score")
'Range("F12").Select
'ActiveCell.Value = InputBox("Enter Participant 11, Test Score")

'Range("B13").Select
'ActiveCell.Value = InputBox("Enter Participant 12 Name")
'Range("C13").Select
'ActiveCell.Value = InputBox("Enter Participant 12, Trial 1 Score")
'Range("D13").Select
'ActiveCell.Value = InputBox("Enter Participant 12, Trial 2 Score")
'Range("E13").Select
'ActiveCell.Value = InputBox("Enter Participant 12, Trial 3 Score")
'Range("F13").Select
'ActiveCell.Value = InputBox("Enter Participant 12, Test Score")

Range("C15").Select
ActiveCell.Formula = "=MEDIAN(C2:C13)"
Range("C16").Select
ActiveCell.Formula = "=PERCENTILE.INC(C2:C13,0.75)-PERCENTILE.INC(C2:C13,0.25)"

Range("D15").Select
ActiveCell.Formula = "=MEDIAN(D2:D13)"
Range("D16").Select
ActiveCell.Formula = "=PERCENTILE.INC(D2:D13,0.75)-PERCENTILE.INC(D2:D13,0.25)"

Range("E15").Select
ActiveCell.Formula = "=MEDIAN(E2:E13)"
Range("E16").Select
ActiveCell.Formula = "=PERCENTILE.INC(E2:E13,0.75)-PERCENTILE.INC(E2:E13,0.25)"

Range("F15").Select
ActiveCell.Formula = "=MEDIAN(F2:F13)"
Range("F16").Select
ActiveCell.Formula = "=PERCENTILE.INC(F2:F13,0.75)-PERCENTILE.INC(F2:F13,0.25)"

'Range("C19").Select
'ActiveCell.Value = InputBox("Enter Course Median for Trial 1")
'Range("D19").Select
'ActiveCell.Value = InputBox("Enter Course Median for Trial 2")
'Range("E19").Select
'ActiveCell.Value = InputBox("Enter Course Median for Trial 3")
'Range("F19").Select
'ActiveCell.Value = InputBox("Enter Course Median for Testing Station")

'Range("C20").Select
'ActiveCell.Value = InputBox("Enter Course Interquartile Range (IQR) for Trial 1")
'Range("D20").Select
'ActiveCell.Value = InputBox("Enter Course Interquartile Range (IQR) for Trial 2")
'Range("E20").Select
'ActiveCell.Value = InputBox("Enter Course Interquartile Range (IQR) for Trial 3")
'Range("F20").Select
'ActiveCell.Value = InputBox("Enter Course Interquartile Range (IQR) for Testing Station")

' Declare the Range of variables to be plotted
Dim Labels As Range
Set Labels = Range("B1:F1")
Dim UsrRng As Range
Set UsrRng = Range("B2:F2")
Dim ClassMedians As Range
Set ClassMedians = Range("B15:F15")
Dim CourseMedians As Range
Set CourseMedians = Range("B19:F19")

Dim myDataRange As Range
Set myDataRange = Application.InputBox( _
Prompt:="Specify the cell range [e.g. Bx:Fy] of the participant you want graphed.", _
Title:="Select a Range of Participant Data", Type:=8)

Dim myChart As ChartObject

With myChart
' Create Bar Chart ("Clustered Column Chart") of Results
Range("B1:F1, & myDataRange, B15:F15, B19:F19").Select
Range("B19").Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range( _
"B1:F1, & myDataRange, B15:F15, B19:F19")
End With

ActiveWorkbook.SaveAs Filename:="practical_simulation_report_example.xlsm"


End Sub
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
You must log in or register to reply here.

Similar threads

W
  • Question
VBA InputBox without entry quitting Sub/Macro
Replies
1
Views
483
Micron
M
C
  • Question
Incorporating a UserForm in place of multiple InputBoxes
Replies
2
Views
326
Fluff
Fluff
SlinkRN
  • Solved
Handling Multiple Errors within one loop
Replies
7
Views
187
SlinkRN
SlinkRN
G
  • Solved
Golf Handicap Formula help
2
Replies
11
Views
399
Gulfcourse
G
JohnZ1156
  • Solved
Copy row in table using a variable number of times.
2
Replies
10
Views
801
johnnyL
J

Forum statistics

Threads
1,221,310
Messages
6,159,173
Members
451,543
Latest member
cesymcox

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