Repeat Macro?

[Hesper]

Hi everyone,

I am a teacher and I am trying to create a grade book in excel where the following conditions exist:
Column B are student names
Column C are student grade averages
Column D is a comment code that relates to the students' averages.

I created the following macro in VBA:

Range("D4").Value = "85"

Case Is < 54.5
Range("D4").Value = "84"

Case Is < 60
Range("D4").Value = "83"

Case Is < 64.5
Range("D4").Value = "82"

Case Is < 70
Range("D4").Value = "81"

Case Is < 74.5
Range("D4").Value = "80"

Case Is < 80
Range("D4").Value = "79"

Case Is < 84.5
Range("D4").Value = "78"

Case Is < 90
Range("D4").Value = "77"

Case Is < 95.5
Range("D4").Value = "76"

Case Is < 100
Range("D4").Value = "75"


Case Is > 100
Range("D4").Value = "75"

Case Is = 100
Range("D4").Value = "75"

Case Else

Range("D4").Value = 0

End Select


What do I do if I need this macro to repeat down column C? Essentially, the values in C should determine what value goes into D on the same row. Is this possible?

Thanks!

 

[Hesper]

To update, I've changed the code a bit and am now able to get the macro to run for a single column but I can only replace information in that column, not place it in a new column:

Sub Change()
Dim Beta As Range, cell As Range

Set Beta = Range("C4:C220")

For Each cell In Beta
Select Case cell.Value
Case Is < 1: cell = " "
Case Is < 50: cell = "code 85"
Case Is < 54.5: cell = "code 84"
Case Is < 60: cell = "code 83"
Case Is < 64.5: cell = "code 82"
Case Is < 70: cell = "code 81"
Case Is < 74.5: cell = "code 80"
Case Is < 80: cell = "code 79"
Case Is < 84.5: cell = "code 78"
Case Is < 90: cell = "code 77"
Case Is < 95.5: cell = "code 76"
Case Is < 100: cell = "code 75"
Case Is > 100: cell = "code 75"
Case Is = 100: cell = "code 75"

End Select

Next

End Sub


What I would like to do is have excel read the values in column C and place a corresponding code in column D.

 

[anomie]

Hi there.

I'm not 100% sure what you mean, but try this.

Instead of replacing the cell value with the code, it writes the code in column D of the same row. (That's what the "offset" business is all about.)

Also I have changed it to a worksheet_change macro, so that you don't have to keep running it as it will automatically run whenever a cell in the C4:C220 range has a new value entered. (that's what the "target" business is all about). Just copy it as it into the worksheet module for the sheet in question. When you enter a value in the C4:C220 range, and then press enter or click away, the code number should automatically appear next to it in column D.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

If Target.Column = 3 And Target.Row > 3 And Target.Row < 221 Then

Set cell = Target

Select Case cell.Value

Case Is < 1: cell.Offset(0, 1) = " "
Case Is < 50: cell.Offset(0, 1) = "code 85"
Case Is < 54.5: cell.Offset(0, 1) = "code 84"
Case Is < 60: cell.Offset(0, 1) = "code 83"
Case Is < 64.5: cell.Offset(0, 1) = "code 82"
Case Is < 70: cell.Offset(0, 1) = "code 81"
Case Is < 74.5: cell.Offset(0, 1) = "code 80"
Case Is < 80: cell.Offset(0, 1) = "code 79"
Case Is < 84.5: cell.Offset(0, 1) = "code 78"
Case Is < 90: cell.Offset(0, 1) = "code 77"
Case Is < 95.5: cell.Offset(0, 1) = "code 76"
Case Is >= 95.5: cell.Offset(0, 1) = "code 75"

End Select

End If

End Sub


Cheers
ani

 

[Hesper]

Thanks so much, Ani! In case you couldn't tell, I'm a bit of a newb when it comes to this stuff. My only problem now is that column C is a formula that automatically calculates the students' averages. For example, C4's formula is:

=IF(ISERROR(SUM(V4+X4+Z4+40)), " ", SUM(V4+X4+Z4+40))

I did this so that I don't get those annoying #VALUE! or #DIV/0! messages but for some reason, the macro is reading any cell that appears blank but has the formula in it as something else. In column D, it is placing "code 76" where I believe it should be blank. How can I fix this?

Thanks again for your help!

Hesper

 

[anomie]

Hello

It seems excel was interpreting the space (" ") you put as your error value as a number. If you thought excel was a bastion of logic then ... ?

The folloiwing code first checks for whether the TEXT of the cell ( different thing from the VALUE) is either empty or a space, and then if it is not either of those, it does the thing with the codes and the cases.

Possible solution below.

Let me know how you get on with it

a


Private Sub Worksheet_Calculate()
Dim c As Range

For Each c In Range("A4:A220")
If c.Text = "" Or c.Text = " " Then
c.Offset(0, 1) = vbNullString
Else

Select Case c.Value

Case Is < 1: c.Offset(0, 1) = " "
Case Is < 50: c.Offset(0, 1) = "code 85"
Case Is < 54.5: c.Offset(0, 1) = "code 84"
Case Is < 60: c.Offset(0, 1) = "code 83"
Case Is < 64.5: c.Offset(0, 1) = "code 82"
Case Is < 70: c.Offset(0, 1) = "code 81"
Case Is < 74.5: c.Offset(0, 1) = "code 80"
Case Is < 80: c.Offset(0, 1) = "code 79"
Case Is < 84.5: c.Offset(0, 1) = "code 78"
Case Is < 90: c.Offset(0, 1) = "code 77"
Case Is < 95.5: c.Offset(0, 1) = "code 76"
Case Is >= 95.5: c.Offset(0, 1) = "code 75"

End Select
End If
Next

End Sub

 

[Hesper]

Thanks again! This is perfect.