Excel VBA VLookup - Error 13 - "Type Mismatch"
I am working on an Excel VBA macro that receives a customer email from another sheet.
I am getting error 13 "Mismatch Type" from 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 do this, it will be fine:
emailadress = Application.VLookup("John Doe", lookup_mail, 4, False)
So I think there is something wrong with the way I am stating? customer name. This is weird because when I print out the client_name in the MsgBox, it pops up fine.
(I am using VBA beacause, I need to send automatic emails to clients)
Any help apreciated!
source to share
I am guessing it is not your VLOOKUP that raises a type mismatch error, but assigns its return value to a string. Your VLOOKUP may be trying to assign an error value to a string and this gives a type mismatch. Try using variant instead.
Also, declaring variables inside the loop burns my eyes. Please, do not do that. The same applies to anything you can actually do in an OUTSIDE loop, such as setting the range of lookup_mail.
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 may contain an error. Check it.
EDIT:
If you are using application.worksheetfunction.vlookup it might work better. If Vlookup throws an error, it just returns the error in VBA and doesn't try to return it to a variable.
source to share