Excel VBA宏

156

戴维·叙利亚克

我有Range("A1:A4")名字和Range("B1:B4")等级。我需要他们的身份Range("C1:C4")。我已经Select Case在VBA中使用过此功能，但它引发了错误：

运行时错误13，类型不匹配

Sub Grades()

Dim mark As Integer

mark = Range("B1:B4").Value

Select Case mark

Case 0 To 35
Range("C1:C4").Value = "Fail"

Case 36 To 59
Range("C1:C4").Value = "Pass"

Case 60 To 79
Range("C1:C4").Value = "Class"

Case 80 To 89
Range("C1:C4").Value = "Distinction"

Case 90 To 100
Range("C1:C4").Value = "Excellent"

Case Else
MsgBox "no text entered"

End Select

End Sub

塞拉利昂奥斯卡

Range("B1:B4").Value将返回一个数组，因此将不适用于Select Case块。

您需要遍历每个值以便分别评估：

For Each cell In Range("B1:B4").Cells
    Select Case cell.Value
        Case 0 To 35:   Range("C" & cell.Row).Value = "Fail"
        Case 36 To 59:  Range("C" & cell.Row).Value = "Pass"
        Case 60 To 79:  Range("C" & cell.Row).Value = "Class"
        Case 80 To 89:  Range("C" & cell.Row).Value = "Distinction"
        Case 90 To 100: Range("C" & cell.Row).Value = "Excellent"
        Case Else:      MsgBox "No text entered"
    End Select
Next

更好的是-只需分配一个公式并重写值即可：

With Range("C1:C4")
    .FormulaR1C1 = "=IF(RC[-1]>0,IF(RC[-1]<36,"Fail",IF(RC[-1]<60,"Pass",IF(RC[-1]<80,"Class",IF(RC[-1]<90,"Distinction",IF(RC[-1]<=100,"Excellent"))))),"No Value Entered")"
    .Value = .Value
End With

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。