Excel VBA VLookup - Error 13 - "Type Mismatch"

Question

I am working on a Excel VBA Macro that gets the email of a client from another sheet.

I am getting Error 13 "Type Mismatch" from the VLookup:

For Each c In Range("D3:D130").Cells

    If c > 500 Then

      Dim emailadress As String
      Dim client_name As String
      Dim lookup_mail As Range

      Set lookup_mail = Sheets("MAILS").Range("A1:D130")

      client_name = Range("A" & (c.Row)).Value

      emailadress = Application.VLookup(client_name, lookup_mail, 4, False)

If I make it this way it works just fine:

      emailadress = Application.VLookup("John Doe", lookup_mail, 4, False)

So I guess there is something is wrong with the way I "declare?" client_name. Which is strange because when I output client_name in a MsgBox it pops up perfectly.

(I use VBA beacause I need to send automatic emails to clients)

Any help apreciated!

Solution

My guess is, it is not your VLOOKUP that is raising the type mismatch error, but assigning its return value to a string. Your VLOOKUP might be trying to assign an error value to a string, and that gives a type mismatch. Try using a variant instead.

Also, declaring variables inside a loop burns my eyes. Please don't do that. Same goes to everything you can actually do OUTSIDE the loop, like setting the lookup_mail range.

Dim emailadress As Variant
Dim client_name As String
Dim lookup_mail As Range
Dim c As Range

Set lookup_mail = Sheets("MAILS").Range("A1:B3")

For Each c In Range("D3:D130").Cells
  If c.Value > 500 Then
    client_name = Range("A" & (c.Row)).Value
    emailadress = Application.VLookup(client_name, lookup_mail, 4, False)
  End If
Next

This way your code will work, but your emailadress variable might contain an error. Check that.

EDIT:

If you use application.worksheetfunction.vlookup, it might work better. If Vlookup raises an error, it just gives back an error in VBA, and is not trying to return it to a variable.