Excel VBA切换语句基于单元格的值

So I have a cell(A2) with an Integer value in it(5). I would like a switch statement that looks at the cell and then if the value of that cell is greater than 50000 do something, if the value is less than 50000 do something.

所以我有一个单元格(A2),其中包含一个Integer值(5)。我想看一个切换语句来查看单元格,然后如果该单元格的值大于50000做某事,如果该值小于50000做某事。

Here is my code that does not work, it says "overflow" for the line of code threshold = 50000:

这是我的代码不起作用,它代码行阈值= 50000的“溢出”:

    Private Sub Worksheet_Change(ByVal Target As Range)

    Application.ScreenUpdating = False

    Dim state As Integer
    Dim threshold As Integer

    threshold = 50000
    state = Sheet1.Range("A2").Value

    Select Case state
        Case state < threshold
            'Do something
            Debug.Print (state)
        Case Is >= threshold
            'Do something
            Debug.Print (state)
        End Select

End Sub

How can I fix this?

我怎样才能解决这个问题?

2 个解决方案

#1

The problem is here:
Sheet1.Range.Value("$A$2")

问题出在这里:Sheet1.Range.Value(“$ A $ 2”)

using Select Case

使用Select Case

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim state As Integer
    state = CInt(Sheet1.Range("$A$2"))
    Select Case state
        Case Is > Target.Value
            Debug.Print "less than " & state
        Case Is < Target.Value
            Debug.Print "greater than " & state
        Case Else
            Debug.Print "Equals"
    End Select
End Sub

Using if statement

使用if语句

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim state As Integer
    state = CInt(Sheet1.Range("$A$2"))
    If Target.Value < state Then
        Debug.Print "less than " & state
    ElseIf Target.Value > state Then
        Debug.Print "greater than " & state
    Else
        Debug.Print "Equals"
    End If
End Sub

POST_EDIT:
50000 is too big for an Integer data type, so dimension it as a Long data type to avoid the Overflow

POST_EDIT:50000对于Integer数据类型来说太大了,因此将其标注为Long数据类型以避免溢出

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim threshold As Long
    threshold = 50000
    Select Case Target.Value
        Case Is >= threshold
            Debug.Print "greater or equal " & threshold
        Case Is < Target.Value
            Debug.Print "smaller than " & threshold
        Case Else
            Debug.Print "Can't calculate! Error"
    End Select
End Sub

#2

Your Range statement is wrong

您的Range语句错误

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.ScreenUpdating = False

    Dim state As Integer

    state = Sheet1.Range("A2").Value

    Select Case state
        Case Is < 5
            'Do something
            Debug.Print (state)
        Case Is > 5
            'Do something

        End Select

End Sub

you didn't mention what to do if state = 5?

如果state = 5,你没有提到该怎么办?

#1