Type Mismatch

  • Thread starter Thread starter Jack Schitt
  • Start date Start date
J

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()
 
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
 
Thanks, that works (after removing the "Not")

Chip Pearson said:
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
Click to expand...
 
Back
Top