CODEWITHSUNDEEP
vbaexcelmatch
Karen88
Karen88

Reputation: 157

Application.match in VBA

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

Upvotes: 3

Views: 63867

Answers (2)

Vityata
Vityata

Reputation: 43595

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

Upvotes: 4

Max
Max

Reputation: 146

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.

Upvotes: 9

Related Questions