为什么在Excel VBA中的case语句中无法访问变量 [英] Why variable is not accessed inside a case statement in Excel VBA

问题描述

我有一个Excel VBA函数，该函数根据A列中的标记为B列中的学生评分.我尝试使用FOR NEXT，它工作正常，但是当我尝试使用DO时，它显示错误.问题出在哪里?

I have a Excel VBA function which gives the grading of a student in the B column based on the marks in the A column. I tried with FOR NEXT and it worked fine but when I tried with DO While it showed error. Where is the issue?

Private Sub Button2_Click()
    Dim mark As Single
    Dim grade As String
    Dim counter As Integer
    counter = 1
    Do While counter < 10
        counter = counter + 1
        mark = Cells(counter, 1).Value
        'To set the alignment to center
        Range("A1:B10").Select
            With Selection
            .HorizontalAlignment = xlCenter
            End With
        Select Case mark
            Case 0 To 20
            grade = "F"
            Cells(counter, 2) = grade
            Case 20 To 29
            grade = "E"
            Cells(counter, 2) = grade
            Case 30 To 39
            grade = "D"
            Cells(counter, 2) = grade
            Case 40 To 59
            grade = "C"
            Cells(counter, 2) = grade
            Case 60 To 79
            grade = "B"
            Cells(counter, 2) = grade
            Case 80 To 100
            grade = "A"
            Cells(counter, 2) = grade
            Case Else
            grade = "Error!"
            Cells(counter, 2) = grade
        End Select
    Loop
End Sub

您可以在Excel工作表A列的前10行中使用0到100之间的任何值.当您运行宏时，B单元格会充满渐变.

You can take any values between 0 to 100 in the first 10 rows of excel sheet A column. When you run the macro the B cell gets filled with gradings.

推荐答案

您的代码对我有用.由于您想倾斜前10行，因此必须在开始时设置counter = 0，或者根本不设置它此外，您可以将其缩短:

your code works for me. since you want to slop through the first 10 rows you have to set counter=0 at the beginning, or not to set it at all furthermore you can shorten it down :

Private Sub Button2_Click()
    Dim mark As Single
    Dim grade As String
    Dim counter As Integer

    Range("A1:B10").HorizontalAlignment = xlCenter
    Do While counter < 10
        counter = counter + 1
        mark = Cells(counter, 1).Value
        'To set the alignment to center
        Select Case mark
            Case 0 To 19
                grade = "F"
            Case 20 To 29
                grade = "E"
            Case 30 To 39
                grade = "D"
            Case 40 To 59
                grade = "C"
            Case 60 To 79
                grade = "B"
            Case 80 To 100
                grade = "A"
            Case Else
                grade = "Error!"
        End Select
        Cells(counter, 2) = grade
    Loop
End Sub

这篇关于为什么在Excel VBA中的case语句中无法访问变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！