Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Type mismatch... | Excel Discussion (Misc queries) | |||
type mismatch--how to fix | Excel Discussion (Misc queries) | |||
Type mismatch | Excel Programming | |||
Type mismatch | Excel Programming | |||
Type Mismatch | Excel Programming |