Type Mismatch
 

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()

--
Return email address is not as DEEP as it appears

Type Mismatch
 

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" wrote in
message ...
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()

--
Return email address is not as DEEP as it appears

Type Mismatch
 

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

"Chip Pearson" wrote in message
...
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" wrote in
message ...
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()

--
Return email address is not as DEEP as it appears