Excel中运行时错误“13”：类型不匹配代码

Question

我不是能把这个错误固定的，它只是一些excel文件我用打转转：

这是在工作表Sheet1（OrderInvoice）：

Private Sub Worksheet_Change(ByVal Target As Range) 

    Dim rng As Range, cell As Range 
    Dim i As Integer 

    Set rng = Sheets("CustomerList").Range("A2:A5") 

    'Sheets("CustomerList").Range("A2:A5").Value 

    'For Each cell In rng 

    'If cell = Range("C10").Value Then 
    If Sheets("CustomerList").Range("A2:A5") = Range("C10").Value Then 
    Sheets("OrderInvoice").Range("C11").Value = Sheets("CustomerList").Range(1, 2).Value 
    Sheets("OrderInvoice").Range("I11").Value = Sheets("CustomerList").Range(1, 4).Value 
    Sheets("OrderInvoice").Range("C12").Value = Sheets("CustomerList").Range(1, 3).Value 
    End If 

    'Next cell 

    'End If 
End Sub 

Answer 1

的分子式为：

对于Cust Name：

=VLookup(C10, 'CustomerList'!A:D, 2, false) 

对于Company：

=VLookup(C10, 'CustomerList'!A:D, 3, false) 

为Contact Number：

=VLookup(C10, 'CustomerList'!A:D, 4, false) 

Answer 2

你可以用Range对象的Find()方法更好

Dim rng As Range 

With Sheets("CustomerList") '<--| reference "CustomerList" sheet 
    With .Range("A2", .Cells(.Rows.count, 1).End(xlUp)) '<--| reference its column A range from row 2 down to last not empty one 
     Set rng = .Find(what:=Range("C10").Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) '<--| search referenced range for "Cust Num" entry 
     If Not rng Is Nothing Then '<--| if found 
      Range("C11").Value = rng.Offset(, 1).Value '<--| fill cell C11 with value in cell 1 column offset the found CustNum 
      Range("C12").Value = rng.Offset(, 2).Value '<--| fill cell C12 with value in cell 2 columns offset the found CustNum 
      Range("I11").Value = rng.Offset(, 3).Value '<--| fill cell I11 with value in cell 3 columns offset the found CustNum 
     End If 
    End With 
End With 

---

此外，从你的屏幕截图我得到的您正尝试在“Form”Shee中管理用户的条目吨。

在这种情况下，我写了这样一个Worksheet_Change()事件处理程序：

Private Sub Worksheet_Change(ByVal Target As Range) 

    Select Case Target.Address(False, False) 
     Case "C10" '<--| user edited "Cust Num" entry 
      FillCustomerInfo Target.Value '<--| call the sub responsible for handling "Customer Number" entry 
     Case "C6" '<--| user edited "Order Number" entry 
      FillOrderNumberInfo Target.Value '<--| call the sub responsible for handling "Order Number" entry 
     Case Else 
    End Select 

End Sub 

，你检查哪个单元已经被用户更改，并启动相应的子来治疗细胞含量相应

在这种情况下，子FillCustomerInfo()将因此是：

Sub FillCustomerInfo(val As Variant) 
    Dim rng As Range 

    With Sheets("CustomerList") '<--| reference "CustomerList" sheet 
     With .Range("A2", .Cells(.Rows.count, 1).End(xlUp)) '<--| reference its column A range from row 2 down to last not empty one 
      Set rng = .Find(what:=val, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) '<--| search referenced range for passed value 
      If Not rng Is Nothing Then '<--| if found 
       Range("C11").Value = rng.Offset(, 1).Value '<--| fill cell C11 with value in cell 1 column offset the found CustNum 
       Range("C12").Value = rng.Offset(, 2).Value '<--| fill cell C12 with value in cell 2 columns offset the found CustNum 
       Range("I11").Value = rng.Offset(, 3).Value '<--| fill cell I11 with value in cell 3 columns offset the found CustNum 
      End If 
     End With 
    End With 
End Sub