Vlookup error in vba

[stmarl]

Run-Time Error '13':
Type mismatch

[I]Private Sub CommandButton2_Click()[/I]
[I]Dim CheckTrack As String[/I]
[I]Dim Vlookupticket As String[/I]

[I]CheckTrack = TicketNumber.Value[/I]
[I]Vlookupticket = Application.VLookup(CheckTrack, Sheet2.Range("A:B"), 2, False)[/I]

[I]If IsError(Vlookupticket) Then[/I]
[I]EscalationTitle.Value = "No Ticket found"[/I]
[I]Else[/I]
[I]EscalationTitle.Value = Vlookupticket[/I]

[I]End If[/I]

[I]End Sub[/I]

 

[mrhstn]

Re: Vlookup error in vba Help Needed

Hi, welcome to the forums

You set a lot of variables there, and relying on Vlookup for your intended result isn't necessary

Private Sub CommandButton2_Click()
Dim findItem As Range


Set findItem = Sheet2.Range("A:A").Find(TicketNumber.Value, Sheet2.Range("A1"))


If Not findItem Is Nothing Then
    EscalationTitle.Value = findItem.Offset(, 1).Value
Else
    EscalationTitle.Value = "No Ticket found"
End If


End Sub

Test this on a copy of your workbook and see if it yields the desired results.

 

[JoeMo]

Re: Vlookup error in vba Help Needed

Where are TicketNumber and EscalationTitle set and valued?
If you Dim VlookupTicket as a string then you will get a type mismatch error. Try Dim VlookupTicket as Variant.

 

[stmarl]

Re: Vlookup error in vba Help Needed

This works!!!! Thank you!!!!

 

[stmarl]

Re: Vlookup error in vba Help Needed

Thanks mrhstn. It Worked!!