Macro To Change Letter Grade To Number Grade

[florinel]

Hi !

I have a range of cells C11:C29,G12:G18,G20:G23,G25:G26,G28:G29,C33:C42,G33:G42,C46:C47,G46:G47,C51:C54,G51:G59,C58:C59 that contains letter grades (egs. A+,C- etc). I need the code for a macro that checks each cell from this range and if the value of the cell is A+ then the cell's value changes to 100, if the cell's value is A then change the cell's value to 98, if cell's value is B+ then change to 88 ...and so on...you get the idea (i will complete the rest of conditions but i just need the basic code).

Thanks.

[Paul]

Hi Florinel, something like this should work for you. Just change the ranges to your ranges, of course. Simply add more 'Case Is' statements for the B's, C's, D's, etc..

Sub updateGrades()
Dim ce As Range
    For Each ce In Union([A1:A6], [C7:C12], [G29:G32])
        Select Case ce.Value
            Case Is = "A+"
                ce.Value = 100
            Case Is = "A"
                ce.Value = 95
            Case Is = "A-"
                ce.Value = 92
        End Select
    Next ce
End Sub

[florinel]

It works great...thanks...one more question though : i forgot to mention to change the value of the cell if the letter grade is "A" or "a" to 98....so, how do i include lower case letters too ?

[Paul]

Florinel, just make one change:

Select Case ce.Value

to

Select Case UCase(ce.Value)

This will force the code to look at the upper case version of whatever is in each cell. You could have also changed each Case Is statement to be:

Case Is = "A+", "a+"
    ce.Value = 100
Case Is = "A", "a"
    ce.Value = 95
etc..

but why change a dozen lines of code when you can change just one?

[florinel]

Yep...it works perfect now...thank you....you saved me a lot of work.