CODEWITHSUNDEEP
vbaexcel
Scott Jethro Hardwick
Scott Jethro Hardwick

Reputation: 65

How to select the cell when case is true

First off, just starting trying to learn Excel and VBA yesterday....so bear in mind.

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

I want to know which cell or the row of the cell that matches my variable. This does not do what I want.......

Thanks guys

Upvotes: 3

Views: 135

Answers (3)

brettdj
brettdj

Reputation: 55702

I appreciate you are learning code. But beyond getting the range syntax correct two better methods (efficiency wise) are shown below

  1. Array

recut

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
  1. Evaluate

From Is it possible to fill an array with row numbers which match a certain criteria without looping?

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

Upvotes: 1

Chris
Chris

Reputation: 186

Try this

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

Upvotes: 0

Scott Jethro Hardwick
Scott Jethro Hardwick

Reputation: 65

For a = 1 To 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

Next a

Upvotes: 0

Related Questions