CODEWITHSUNDEEP
excelvba
Finklie Fuerst
Finklie Fuerst

Reputation: 11

Expected function or variable error using the "Find" function

Public Sub CheckAvailability()

Dim ALU As String
Dim Availability As Integer
Dim Rnge As Range
ALU = InputBox("Input in the ALU to search", "Search ALU")
If Trim(ALU) <> "" Then
   With Sheets("Sheets2").Range("A:A")
   Set Rnge = .Find(what:=ALU, After:=Cells(.Cells.Count), Lookin:=xlValues, LookAt:=xlWhole, 
                    Searchorder:=x1byRows, SearchDirection:=xlNext, MatchCase:=False)
            If Not Rnge Is Nothing Then
                Availability = Application.Goto(Rnge).EntireRow.Cells(5)
                If Availability > 0 Then
                    MsgBox ("The Availability is" + Availability)
                Else
                   MsgBox (" There is none Available")
            Else 
                MSGbox("This ALU does not exist in this report")
            End If
        End With
     End If
End Sub

The objective of the code is to allow the user to input a special item code (ALU), then search for the ALU, check whether the ALU actually exists, and give the corresponding availability of the item from another column. When I try to run the code it highlights the "Goto" and gives me that error message. Any ideas?

Upvotes: 0

Views: 37

Answers (1)

Harun24hr
Harun24hr

Reputation: 36780

If you want to return the value from 5 column from find cell then use Offset() of Rnge like Rnge.Offset(, 5). Try below codes.

Public Sub CheckAvailability()
Dim ALU As String
Dim Availability As Integer
Dim Rnge As Range

ALU = InputBox("Input in the ALU to search", "Search ALU")
If Trim(ALU) <> "" Then
   With Sheets("Sheet1").Range("A:A")
   Set Rnge = .Find(What:=ALU, _
                After:=.Range("A1"), _
                Lookat:=xlWhole, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False)
            If Not Rnge Is Nothing Then
                Availability = Rnge.Offset(, 5)
                If Availability > 0 Then
                    MsgBox ("The Availability is: " & Availability)
                Else
                   MsgBox (" There is none Available")
                End If
            Else
                MsgBox ("This ALU does not exist in this report")
            End If
        End With
     End If
End Sub

Upvotes: 1

Related Questions