Accepting input from user

[Christol]

I am developing a student grades report on excel for my students. I need to accept a number from the user(the number of students in the class) and allocate so many cells for various fields such as name, age, grades. To calculate grades, I need the formula to be automatically copied to as many particular cells as there are students present.

for example, if user inputs as shown below,

Number of students = 30

then automatically 30 cells should be allocated for various fields such as

Number NAME age GRADES
1 I will write the formula only for first cell
2 these formulae should be copied automatically
3 copied automatically
4
...

29
30


How can this be done?

 

[GTO]

Hi there,

You did not include the formula you want to use, so just a basic example.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> exa2()<br><SPAN style="color:#00007F">Dim</SPAN> rngScores <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> dblNumOfStudents <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN><br>    <br>    dblNumOfStudents = Application.InputBox("Enter the number of students", "Scores", , , , , , 1)<br>    <br>    <SPAN style="color:#00007F">If</SPAN> dblNumOfStudents >= 1 And dblNumOfStudents - <SPAN style="color:#00007F">CLng</SPAN>(dblNumOfStudents) = 0 <SPAN style="color:#00007F">Then</SPAN><br>        <br>        <SPAN style="color:#00007F">Set</SPAN> rngScores = ThisWorkbook.Worksheets("MySheet").Range("A1:D" & dblNumOfStudents + 1)<br>        <br>        rngScores.Rows(1).Value = Array("Number", "Name", "Age", "Grade")<br>        <br>        rngScores.Offset(1, 3).Resize(rngScores.Rows.Count - 1, 1).Formula _<br>            = "=IF(A2>89,""A"",IF(A2>79,""B"", IF(A2>69,""C"",IF(A2>59,""D"",""F""))))"<br>    <SPAN style="color:#00007F">Else</SPAN><br>        MsgBox "You must enter a whole number.", 0, vbNullString<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[MickG]

This code will enter Numbers 1 to the "Number of Students" (Code Variable NofS), in column "A" starting "A2, and Copy the formula in column "D" row(2), down the same number od rows.

[COLOR="Navy"]Sub[/COLOR] MG03May42
[COLOR="Navy"]Dim[/COLOR] NofS [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
NofS = 30
[COLOR="Navy"]With[/COLOR] Range("A2")
    .value = 1
    .Resize(NofS).DataSeries
        [COLOR="Navy"]With[/COLOR] .Offset(, 3)
            .Resize(NofS).Formula = .Formula
        [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Christol]

Thanks for your suggestions. I am a newbie to Excel and hence I dont have enough knowledge of using macros, so I am not able to use these codes that you've provided. Can you tell me how to use these codes in excel or can you please direct me to a good reference on incorporating these codes into Excel?

 

[GTO]

I am not sure about 2007+, hopefully this still works. Press ALT + F11 to bring up VBIDE. In the menubar, Insert|Module. In the code window (the big white one with nothing in it), paste the code.

Make sure that you have a blank sheet selected in the workbook (Actually, just test the codes in a new/blank wb) and from the menubar Tools|Macros|Macro which brings up the macro dialog. Select which code you want to try and press the Run button.