Excel VBA VLookup - 错误13 - “类型不匹配” [英] Excel VBA VLookup - Error 13 - "Type Mismatch"

问题描述

我正在处理一个Excel VBA宏，从另一张表中获取客户端的电子邮件。

我从VLookup中获取错误13类型不匹配：

 对于每个c范围内（D3：D130）。单元格
 
如果c> 500然后
 
 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））值
 
 emailadress = Application.VLookup（client_name，lookup_mail，4 ，False）
  

如果我这样做，它的工作原理很好：

  emailadress = Application.VLookup（John Doe，lookup_mail，4，False）
  

所以我想我的声明方式有什么问题吗？客户名称。这是奇怪的，因为当我在MsgBox中输出client_name时，它会完美弹出。

（我使用VBA beacause我需要向客户端发送自动电子邮件）

任何帮助apreciated！

解决方案

我的猜测是，这不是你的VLOOKUP提高类型不匹配错误，但将其返回值分配给字符串。您的VLOOKUP可能会尝试为字符串分配一个错误值，这会导致类型不匹配。

此外，在循环内声明变量会烧伤我的眼睛。请不要这样做。相同的一切，你可以做的事情，除了循环，如设置lookup_mail范围。

  Dim emailadress As Variant 
 Dim client_name As String 
 Dim lookup_mail As Range 
 Dim c As Range 
 
设置lookup_mail =表（MAILS）。范围（A1：B3）
 
每个c范围内（D3：D130）。单元格
如果c.Value> 500然后
 client_name = Range（A&（c.Row））。value 
 emailadress = Application.VLookup（client_name，lookup_mail，4，False）
 End If 
下一个
  

这样你的代码可以工作，但是你的emailadress变量可能会包含一个错误。检查。

编辑：

如果您使用application.worksheetfunction.vlookup，它可能会更好。如果Vlookup引发错误，它只会在VBA中发回错误，而不是尝试将其返回到变量。

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!

解决方案

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.

这篇关于Excel VBA VLookup - 错误13 - “类型不匹配”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！