PDA

View Full Version : Simple Array Help.


Elition
03-07-2008, 07:25 AM
I'm pretty sure it should be filling the array, but it's not outputting :((

Option Explicit
Const s As Long = 100
Const r As Long = 505
Dim ClassesAtt(s) As Double
Dim ClassesPass(s) As Double
Dim CreditAtt(s) As Double
Dim CreditPass(s) As Double
Dim GP(s) As Double
Dim GPA(s) As Double
Dim ID(r) As Long
Dim Credits(r) As Long
Dim Grade(r) As String
Dim m As Long
Dim u As Long
Dim t As Long
Dim j As Long
Dim e As Long
Dim GradePoints(s) As Long









Public Sub studentinfo()

'create talble headings

Range("M1") = "Student ID"
Range("N1") = "Number Classes Attempted"
Range("O1") = "Number Classes Passed"
Range("P1") = "Stundent ID"
Range("Q1") = "Number of Credits Gained"
Range("R1") = "Grade Point Average"

'clear contents
Range("m2", "r65000").ClearContents


For e = 1 To r

'read in the ID numbers
ID(r) = Range("B1").Offset(e, 0).Value

'read in the credits
Credits(r) = Range("E1").Offset(e, 0).Value

'read in the grade
Grade(r) = Range("F1").Offset(e, 0).Value

If Grade(e) = "A" Then

ClassesAtt(ID(r)) = ClassesAtt(ID(r)) + 1
ClassesPass(ID(r)) = ClassesPass(ID(r)) + 1
CreditAtt(ID(r)) = CreditAtt(ID(r)) + Credits(r)
CreditPass(ID(r)) = CreditPass(ID(r)) + Credits(r)
GP(ID(r)) = GP(ID(r)) + 4 * Credits(r)

End If

If Grade(e) = "B" Then

ClassesAtt(ID(r)) = ClassesAtt(ID(r)) + 1
ClassesPass(ID(r)) = ClassesPass(ID(r)) + 1
CreditAtt(ID(r)) = CreditAtt(ID(r)) + Credits(r)
CreditPass(ID(r)) = CreditPass(ID(r)) + Credits(r)
GP(ID(r)) = GP(ID(r)) + 3 * Credits(r)

End If


If Grade(e) = "C" Then

ClassesAtt(ID(r)) = ClassesAtt(ID(r)) + 1
ClassesPass(ID(r)) = ClassesPass(ID(r)) + 1
CreditAtt(ID(r)) = CreditAtt(ID(r)) + Credits(r)
CreditPass(ID(r)) = CreditPass(ID(r)) + Credits(r)
GP(ID(r)) = GP(ID(r)) + 2 * Credits(r)

End If


If Grade(e) = "D" Then

ClassesAtt(ID(r)) = ClassesAtt(ID(r)) + 1
ClassesPass(ID(r)) = ClassesPass(ID(r)) + 1
CreditAtt(ID(r)) = CreditAtt(ID(r)) + Credits(r)
CreditPass(ID(r)) = CreditPass(ID(r)) + Credits(r)
GP(ID(r)) = GP(ID(r)) + 1 * Credits(r)

End If


If Grade(e) = "F" Then

ClassesAtt(ID(r)) = ClassesAtt(ID(r)) + 1
ClassesPass(ID(r)) = ClassesPass(ID(r))
CreditAtt(ID(r)) = CreditAtt(ID(r)) + 1
CreditPass(ID(r)) = CreditPass(ID(r))
GP(ID(r)) = GP(ID(r))

End If


If Grade(e) = "W" Then

ClassesAtt(ID(r)) = ClassesAtt(ID(r)) + 1
ClassesPass(ID(r)) = ClassesPass(ID(r))
CreditAtt(ID(r)) = CreditAtt(ID(r)) + 1
CreditPass(ID(r)) = CreditPass(ID(r))
GP(ID(r)) = GP(ID(r))

End If
Next e

For u = 1 To s

If CreditAtt(u) = 0 Then
GPA(u) = 0

Else
GPA(u) = GradePoints(u) / CreditAtt(u)

End If

Next u

End Sub
Public Sub studentgrades()

m = 1

For t = 1 To s

If ClassesPass(t) < 3 Then
Range("M1").Offset(m, 0).Value = t
Range("N1").Offset(m, 0).Value = ClassesAtt(t)
Range("O1").Offset(m, 0).Value = ClassesPass(t)
m = m + 1

End If
Next t
m = 1

For j = 1 To s

If CreditPass(j) > 14 And GPA(j) > 3.7 Then

Range("P1").Offset(m, 0).Value = j
Range("Q1").Offset(m, 0).Value = CreditPass(j)
Range("R1").Offset(m, 0).Value = GPA(j)
m = m + 1

End If
Next j




End Sub
RonMcK
03-07-2008, 07:58 AM
Please upload a workbook with a small set of 'sanitized' data to use in testing your VBA.

Thanks!
thomaspatton
03-07-2008, 07:59 AM
I'm pretty sure it should be filling the array, but it's not outputting :(

Did you try buying it dinner and saying it looked nice? :rofl:

Anyway, try this and see if it works for ya...


Option Explicit
Const s As Long = 100
Const r As Long = 505
Dim ClassesAtt(s) As Double
Dim ClassesPass(s) As Double
Dim CreditAtt(s) As Double
Dim CreditPass(s) As Double
Dim GP(s) As Double
Dim GPA(s) As Double
Dim ID(r) As Long
Dim Credits(r) As Long
Dim Grade(r) As String
Dim m As Long
Dim u As Long
Dim t As Long
Dim j As Long
Dim e As Long
Dim GradePoints(s) As Long
Public Sub StudentInfo()
With Worksheets("YourSheetNameHere")
'create table headings
.Range("M1").Value = "Student ID"
.Range("N1").Value = "Number Classes Attempted"
.Range("O1").Value = "Number Classes Passed"
.Range("P1").Value = "Stundent ID"
.Range("Q1").Value = "Number of Credits Gained"
.Range("R1").Value = "Grade Point Average"
'clear contents
.Range("m2", "r65000").ClearContents
For e = 1 To r
'read in the ID numbers
ID(r) = .Range("B1").Offset(e, 0).Value
'read in the credits
Credits(r) = .Range("E1").Offset(e, 0).Value
'read in the grade
Grade(r) = .Range("F1").Offset(e, 0).Value
If Grade(e) = "A" Then
ClassesAtt(ID(r)) = ClassesAtt(ID(r)) + 1
ClassesPass(ID(r)) = ClassesPass(ID(r)) + 1
CreditAtt(ID(r)) = CreditAtt(ID(r)) + Credits(r)
CreditPass(ID(r)) = CreditPass(ID(r)) + Credits(r)
GP(ID(r)) = GP(ID(r)) + 4 * Credits(r)
End If
If Grade(e) = "B" Then
ClassesAtt(ID(r)) = ClassesAtt(ID(r)) + 1
ClassesPass(ID(r)) = ClassesPass(ID(r)) + 1
CreditAtt(ID(r)) = CreditAtt(ID(r)) + Credits(r)
CreditPass(ID(r)) = CreditPass(ID(r)) + Credits(r)
GP(ID(r)) = GP(ID(r)) + 3 * Credits(r)
End If
If Grade(e) = "C" Then
ClassesAtt(ID(r)) = ClassesAtt(ID(r)) + 1
ClassesPass(ID(r)) = ClassesPass(ID(r)) + 1
CreditAtt(ID(r)) = CreditAtt(ID(r)) + Credits(r)
CreditPass(ID(r)) = CreditPass(ID(r)) + Credits(r)
GP(ID(r)) = GP(ID(r)) + 2 * Credits(r)
End If
If Grade(e) = "D" Then
ClassesAtt(ID(r)) = ClassesAtt(ID(r)) + 1
ClassesPass(ID(r)) = ClassesPass(ID(r)) + 1
CreditAtt(ID(r)) = CreditAtt(ID(r)) + Credits(r)
CreditPass(ID(r)) = CreditPass(ID(r)) + Credits(r)
GP(ID(r)) = GP(ID(r)) + 1 * Credits(r)
End If
If Grade(e) = "F" Then
ClassesAtt(ID(r)) = ClassesAtt(ID(r)) + 1
ClassesPass(ID(r)) = ClassesPass(ID(r))
CreditAtt(ID(r)) = CreditAtt(ID(r)) + 1
CreditPass(ID(r)) = CreditPass(ID(r))
GP(ID(r)) = GP(ID(r))
End If
If Grade(e) = "W" Then
ClassesAtt(ID(r)) = ClassesAtt(ID(r)) + 1
ClassesPass(ID(r)) = ClassesPass(ID(r))
CreditAtt(ID(r)) = CreditAtt(ID(r)) + 1
CreditPass(ID(r)) = CreditPass(ID(r))
GP(ID(r)) = GP(ID(r))
End If
Next e

For u = 1 To s
If CreditAtt(u) = 0 Then
GPA(u) = 0
Else
GPA(u) = GradePoints(u) / CreditAtt(u)
End If
Next u
End Sub
Public Sub studentgrades()
m = 1
For t = 1 To s
If ClassesPass(t) < 3 Then
.Range("M1").Offset(m, 0).Value = t
.Range("N1").Offset(m, 0).Value = ClassesAtt(t)
.Range("O1").Offset(m, 0).Value = ClassesPass(t)
m = m + 1
End If
Next t
m = 1
For j = 1 To s
If CreditPass(j) > 14 And GPA(j) > 3.7 Then
.Range("P1").Offset(m, 0).Value = j
.Range("Q1").Offset(m, 0).Value = CreditPass(j)
.Range("R1").Offset(m, 0).Value = GPA(j)
m = m + 1
End If
Next j
End Sub


I didn't really change your references or variables, but I did add the :
With Worksheets("YourSheetNameHere")
which you will need to change to your sheet name and updated the Ranges to .Range. Hope this helps, but I couldn't do much more than that because I didn't have a workbook example and my brain is mushy and slow.
MikeO
03-07-2008, 08:02 AM
I haven't tried the code, but it looks like all of the arrays in the "For e = 1 to r" loop have "(r)" as the element. I think you meant to use elements "(e)".
For example,
ID(r) = Range("B1").Offset(e, 0).Value

should be
ID(e) = Range("B1").Offset(e, 0).Value


Hopefully that helps you.
PaulH
03-07-2008, 10:38 AM
Mike I just noticed that your If statements for A through W have the first four lines common. you can put them outside the IF's and just If the grade point average against A through W. Your code would be more efficient.
Good Luck,
Paul
RonMcK
03-07-2008, 12:07 PM
I believe by adding a variable (pts) and changing to use Select Case/End Select constructions you can shrink your code. Here's what I think will work for you:
Option Explicit
Const s As Long = 100
Const r As Long = 505
Dim ClassesAtt(s) As Double
Dim ClassesPass(s) As Double
Dim CreditAtt(s) As Double
Dim CreditPass(s) As Double
Dim GP(s) As Double
Dim GPA(s) As Double
Dim ID(r) As Long
Dim Credits(r) As Long
Dim Grade(r) As String
Dim m As Long
Dim u As Long
Dim t As Long
Dim j As Long
Dim e As Long
Dim GradePoints(s) As Long
Dim pts As Long

Public Sub studentinfo()

'create table headings

Range("M1") = "Student ID"
Range("N1") = "Number Classes Attempted"
Range("O1") = "Number Classes Passed"
Range("P1") = "Stundent ID"
Range("Q1") = "Number of Credits Gained"
Range("R1") = "Grade Point Average"

'clear contents
Range("m2", "r65000").ClearContents


For e = 1 To r

'read in the ID numbers
ID(e) = Range("B1").Offset(e, 0).Value

'read in the credits
Credits(e) = Range("E1").Offset(e, 0).Value

'read in the grade
Grade(e) = Range("F1").Offset(e, 0).Value

Select Case Grade(e)
Case "A"
pts = 4
Case "B"
pts = 3
Case "C"
pts = 2
Case "D"
pts = 1
Case "F"
pts = 0
Case "W"
pts = 0
End Select

ClassesAtt(ID(e)) = ClassesAtt(ID(e)) + 1
ClassesPass(ID(e)) = ClassesPass(ID(e)) + 1
CreditAtt(ID(e)) = CreditAtt(ID(e)) + (pts <> 0) * Credits(e)
CreditPass(ID(e)) = CreditPass(ID(e)) + (pts <> 0) * Credits(e)
GP(ID(e)) = GP(ID(e)) + pts * Credits(e)

Next e

For u = 1 To s

If CreditAtt(u) = 0 Then
GPA(u) = 0

Else
GPA(u) = GradePoints(u) / CreditAtt(u)

End If

Next u

End Sub

Public Sub studentgrades()

m = 1

For t = 1 To s

If ClassesPass(t) < 3 Then
Range("M1").Offset(m, 0).Value = t
Range("N1").Offset(m, 0).Value = ClassesAtt(t)
Range("O1").Offset(m, 0).Value = ClassesPass(t)
m = m + 1
End If
Next t
m = 1

For j = 1 To s
If CreditPass(j) > 14 And GPA(j) > 3.7 Then
Range("P1").Offset(m, 0).Value = j
Range("Q1").Offset(m, 0).Value = CreditPass(j)
Range("R1").Offset(m, 0).Value = GPA(j)
m = m + 1
End If
Next j

End Sub
Cheers!