Application.match in VBA

Question

I'm using "Application.match" to find if Column AD of a sheet contains "1".

But whenever I try to run my code, "Else" condition is always True. Although I'm expecting it to enter the "If" condition, since Column AD has a cell with a value "1".

The values of the cells in Column AD are mostly results of a formula, except for AD1, which has the string "Check if Titile&Author Match", and AD2, which has the value "0".

What is the problem. What is the solution for it?

I hope you can suggest a solution that would still use the ".match" method. This is because from my understanding ".match" method returns the relative position of the match in the range. And I plan to use that value later on in my code.

Private Sub CmdLocateDta_Click()

Dim SearchColumn As Integer

If Not IsError(Application.Match(1, "AD:AD", 0)) Then
    SearchColumn = Application.Match(1, "AD", 0)
    MsgBox "Data has been located." & vbNewLine & _
           "You can now input the Lending Information below."
Else
    MsgBox "There seems to be no such book in the Database." & _
            vbNewLine & "Please re-check your input."
End If

End Sub

Answer 1

The problem is the way you use Application.Match. It should be like this:

If Not IsError(Application.Match(1, Range("AD:AD"), 0)) Then

See more here: https://msdn.microsoft.com/en-us/library/office/ff835873.aspx

Answer 2

You need to specify the range where to search in both Matches.

Application.Match(1, ws.range("AD:AD"),0)

where ws is your worksheet.