当条件为真时如何选择单元格

Question

首先，我昨天才开始尝试学习Excel和VBA...所以请您理解。

Private Sub CommandButton2_Click()

For a = 1 To myLastRow
    Select Case ActiveWorkbook.Sheets("Sheet2").Cells(a, 2).Value
        Case Is = myOrderNumber
            ActiveWorkbook.Sheets("Sheet2").Cells(a, 2).Active
        Case False: MsgBox "False"
    End Select
Next a
End Sub

我想知道哪个单元格或行与我的变量匹配。这并不是我想要的结果......

谢谢大家

- Scott Jethro Hardwick

Answer 1

我很欣赏你正在学习编程。但除了正确使用范围语法外，下面展示了两种更好的方法（从效率上来看）

1. 数组

重新切割

Private Sub CommandButton2_Click()
Dim myLastRow As Long
Dim myOrderNumber As Long
Dim lngCnt As Long
Dim ws As Worksheet
Dim X

myOrderNumber = 2

Set ws = ActiveWorkbook.Sheets("Sheet2")

X = ws.Range(ws.[b1], ws.[b10])
For lngCnt = 1 To UBound(X)
If X(lngCnt, 1) = myOrderNumber Then MsgBox "True " & lngCnt
Next

End Sub

2. 评估

来自在不使用循环的情况下如何填充与特定条件匹配的行号数组？

myOrderNumber = 2
MsgBox Join(Filter(Application.Transpose(Application.Evaluate("=IF(B1:B10=" & myOrderNumber & ",ROW(B1:B10),""x"")")), "x", False), ",")

- brettdj

Answer 2

试试这个

Private Sub CommandButton2_Click()
Dim myLastRow As Long, a As Long, myOrderNumber As Long

myLastRow = 10
For a = 1 To myLastRow
    With ActiveWorkbook.Sheets("Sheet2")
        If .Cells(a, 2).Value = myOrderNumber Then
            MsgBox "True " & .Cells(a, 2).Row
        Else
            Msgbox "False"
        End If
    End With
Next a
End Sub

- Chris

Answer 3

对于 a = 1 到 myLastRow

With ActiveWorkbook.Sheets("Sheet2")
    If .Cells(a, 2).Value = myOrderNumber Then
        myRow = .Cells(a, 2).Row
        MsgBox "True " & myRow
    Else
        'MsgBox "False "
    End If
End With

接下来是a

- Scott Jethro Hardwick