Type Mismatch

[Jack Schitt]

Hi. I get a Type Mismatch run-time error on execution of the following, at
the line commencing "If ..."
Help?? Thanks

Sub TfrData()
Dim wbS As Workbook
Dim wbD As Workbook
Dim wsS As Worksheet
Dim wsD As Worksheet
Dim rD As Range
Dim lrS As Long
Dim lrD As Long
With Application
Set wbS = .Workbooks("ClientList.XLS")
Set wbD = .Workbooks("ClientBase new.xls")
Set wsS = wbS.Worksheets("ClientList")
Set wsD = wbD.Worksheets("Clients")
Set rD = wsD.Range("Number_Range")
lrD = 857
With wsS
For lrS = 2 To 937
If Not Application.Match(.Cells(lrS, 3).Value, rD, 0) Then
lrD = lrD + 1
wsD.Cells(lrD, 13).Value = .Cells(lrS, 2)
wsD.Cells(lrD, 14).Value = .Cells(lrS, 2)
wsD.Cells(lrD, 15).Value = .Cells(lrS, 6)
wsD.Cells(lrD, 16).Value = .Cells(lrS, 1)
wsD.Cells(lrD, 22).Value = .Cells(lrS, 5)
End If 'Not Application.Match(.Cells(lrS, 3).Value, rD, 0)
Next lrS
End With 'wsS
End With 'Application
End Sub 'TfrData()

 

[Chip Pearson]

If Application.Match fails, it returns a variant value containing
an Error subtype value, and you can't use Not with an error
variable. Try changing

If Not Application.Match(.Cells(lrS, 3).Value, rD, 0) Then
' to
If Not IsError(Application.Match(.Cells(lrS,3).Value, rD,0)) Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Jack Schitt]

Thanks, that works (after removing the "Not")

If Application.Match fails, it returns a variant value containing
an Error subtype value, and you can't use Not with an error
variable. Try changing

If Not Application.Match(.Cells(lrS, 3).Value, rD, 0) Then
' to
If Not IsError(Application.Match(.Cells(lrS,3).Value, rD,0)) Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com