时间戳

首页 > 解决方案 > 选择案例结构

excel - 选择案例结构

问题描述

我的问题如下，以下“选择案例”，范围在 A1:A100 之间。在我尝试以下代码后，我只获得了我需要的 100 个进度结果的一个结果。代码如下：

Private Sub CommandButton1_Click()

Dim score As Integer, result As String
 score = Range("A1").Value

Select Case score
    Case Is >= 80
    result = "very good"
    Case Is >= 70
    result = "good"
    Case Is >= 60
    result = "sufficient"
    Case Else
    result = "insufficient"
End Select

    Range("B1").Value = result

End Sub

标签： excelvba

解决方案

如果您指定要对已定义范围内的每个单元格执行相同的功能，则可以执行FOR EACH如下循环：

Private Sub CommandButton1_Click()

Dim score As range, result As String
 set score = Range("A1:a100")

for each c in score.cells

Select Case c.value
    Case Is >= 80
    result = "very good"
    Case Is >= 70
    result = "good"
    Case Is >= 60
    result = "sufficient"
    Case Else
    result = "insufficient"
End Select

    c.offset(0,1) = result  'specifies the cell 1 column to the right of the current cell

next c
End Sub

推荐阅读