CODEWITHSUNDEEP
excelvbasearchworksheet
Chris McCartney
Chris McCartney

Reputation: 1

VBA Search on different Worksheet failing

Sorry for what is probably a stupid question. I am trying to use VBA search in Excel on a different sheet, I was getting somewhere, then it just stopped working.

Public Sub SearchForStudent()

    'Declare vars
    Dim StudentId As String
    Dim rgFound As Range
    Dim ws As Worksheet: Set ws = Sheets("AllStudents")
    Dim rngLook As Range: Set rngLook = ws.Range("P:P")
    Dim FoundRow As Integer
    
    'Set student ID from dropdown in B2
    StudentId = Range("B2").Value
    
    'Look up the cell that it is in
    Set rgFound = rngLook.Find(StudentId)
    
    'Find the row of the result
    FoundRow = rgFound.Row
    
    MsgBox FoundRow

End Sub

FoundRow doesn't get set, even though I know the data is there.

Any help would be greatly appreciated.

Chris

Upvotes: 0

Views: 45

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

Couple of things.

  1. Avoid using Integer when working with rows. Excel 2007+ has 1048576 rows. So we should always declare the variable which will hold the row value as Long instead of Integer. If you try storing say, 1048576 to Integer Variable then you will get an Overflow error.

  2. .Find remembers it's last setting. See the complete syntax at Range.Find method (Excel). So it is better to fully specify the parameters.

  3. When using .Find, always check if .Find returned something to avoid breaking the code.

Is this what you are trying? (UNTESTED)

Public Sub SearchForStudent()
    'Declare vars
    Dim StudentId As String
    Dim rgFound As Range
    Dim ws As Worksheet: Set ws = Sheets("AllStudents")
    Dim rngLook As Range: Set rngLook = ws.Range("P:P")
    Dim FoundRow As Long
    
    'Set student ID from dropdown in B2
    '~~> Ensure the below range is fully qualified
    StudentId = Range("B2").Value
    
    'Look up the cell that it is in
    '~~> Adjust the parameters as you want them
    Set rgFound = rngLook.Find(What:=StudentId, _
                               Lookat:=xlPart, _
                               LookIn:=xlFormulas, _
                               SearchOrder:=xlByRows, _
                               SearchDirection:=xlPrevious, _
                               MatchCase:=False)
        
    '~~> Check if the Find returned anything
    If Not rgFound Is Nothing Then
        FoundRow = rgFound.Row
        MsgBox FoundRow
    Else
        MsgBox "Not Found"
    End If
End Sub

Upvotes: 1

Related Questions