Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch
I get a type mismatch with the first code and still doesnt run with second,
clearly missing something, help is much appreciated with worksheets("sheet1") If Application.match(.Cells(i, 2), wipwkbk.Worksheets("Live").Range("b1:b18"), 0) 0 Then .Cells(i, 1).EntireRow.Copy Destination:=Worksheets("Sheet7").Cells(j,1) OR with worksheets("sheet1") If Application.match(.Cells(i, 2), wipwkbk.Worksheets("Live").Range("b1:b18"), 0) Then .Cells(i, 1).EntireRow.Copy Destination:=Worksheets("Sheet7").Cells(j,1) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch
Teresa,
I tidied it up a bit and that worked fine for me With Worksheets("sheet1") If Application.Match(.Cells(i, 2), _ wipwkbk.Worksheets("Live").Range("b1:b18"), 0) 0 Then .Cells(i, 1).EntireRow.Copy Destination:=Worksheets("Sheet7").Cells(j, 1) End If End With What have you got in wipwkbk, i and j variables? -- HTH RP (remove nothere from the email address if mailing direct) "teresa" wrote in message ... I get a type mismatch with the first code and still doesnt run with second, clearly missing something, help is much appreciated with worksheets("sheet1") If Application.match(.Cells(i, 2), wipwkbk.Worksheets("Live").Range("b1:b18"), 0) 0 Then .Cells(i, 1).EntireRow.Copy Destination:=Worksheets("Sheet7").Cells(j,1) OR with worksheets("sheet1") If Application.match(.Cells(i, 2), wipwkbk.Worksheets("Live").Range("b1:b18"), 0) Then .Cells(i, 1).EntireRow.Copy Destination:=Worksheets("Sheet7").Cells(j,1) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch
problem is that MATCH, anf the Lookup functions error out if there's no
match...so trap them in a "safe" function.... With Worksheets("sheet1") If MyMatch(.Cells(i, 2).Value, Worksheets("Live").Range("b1:b18")) Then .Cells(i, 1).EntireRow.Copy Destination:=Worksheets("Sheet7").Cells(j, 1) End If End With End Sub Function MyMatch(what As String, where As Range) As Boolean On Error Resume Next Dim result As Long result = Application.WorksheetFunction.Match(what, where, 0) On Error GoTo 0 MyMatch = (result 0) End Function "teresa" wrote: I get a type mismatch with the first code and still doesnt run with second, clearly missing something, help is much appreciated with worksheets("sheet1") If Application.match(.Cells(i, 2), wipwkbk.Worksheets("Live").Range("b1:b18"), 0) 0 Then .Cells(i, 1).EntireRow.Copy Destination:=Worksheets("Sheet7").Cells(j,1) OR with worksheets("sheet1") If Application.match(.Cells(i, 2), wipwkbk.Worksheets("Live").Range("b1:b18"), 0) Then .Cells(i, 1).EntireRow.Copy Destination:=Worksheets("Sheet7").Cells(j,1) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch
Well Teresa,
From where did you get Application.Match ? Try following code instead : If Not wipwkbk.Worksheets("Live").Range("b1:b18") _ .Find(.Cells(i, 2)) Is Nothing Then .Cells(i, 1).EntireRow.Copy _ Destination:=Worksheets("Sheet7").Cells(j, 1) End If Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch
Application.Match
often used "shortcut" for worksheet functions eg Application.VLookUp instead of Application.WorksheetFunction.VLookUp NOT SAFE. Always use the full syntax. "Sharad" wrote: Well Teresa, From where did you get Application.Match ? Try following code instead : If Not wipwkbk.Worksheets("Live").Range("b1:b18") _ .Find(.Cells(i, 2)) Is Nothing Then .Cells(i, 1).EntireRow.Copy _ Destination:=Worksheets("Sheet7").Cells(j, 1) End If Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch
Thanks Patrick,
I already guessed it from your earlier post. Sharad "Patrick Molloy" wrote in message ... Application.Match often used "shortcut" for worksheet functions eg Application.VLookUp instead of Application.WorksheetFunction.VLookUp NOT SAFE. Always use the full syntax. "Sharad" wrote: Well Teresa, From where did you get Application.Match ? Try following code instead : If Not wipwkbk.Worksheets("Live").Range("b1:b18") _ .Find(.Cells(i, 2)) Is Nothing Then .Cells(i, 1).EntireRow.Copy _ Destination:=Worksheets("Sheet7").Cells(j, 1) End If Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch
I like to use application.match()
dim res as variant with worksheets("sheet1") res = Application.match(.Cells(i, 2), _ wipwkbk.Worksheets("Live").Range("b1:b18"), 0) if iserror(res) then 'what should happen else if isnumeric(res) then if res 0 then .cells(i,1).entirerow.copy _ Destination:=Worksheets("Sheet7").Cells(j,1) else 'do nothing??? end if else 'do nothing end if end if end with teresa wrote: I get a type mismatch with the first code and still doesnt run with second, clearly missing something, help is much appreciated with worksheets("sheet1") If Application.match(.Cells(i, 2), wipwkbk.Worksheets("Live").Range("b1:b18"), 0) 0 Then .Cells(i, 1).EntireRow.Copy Destination:=Worksheets("Sheet7").Cells(j,1) OR with worksheets("sheet1") If Application.match(.Cells(i, 2), wipwkbk.Worksheets("Live").Range("b1:b18"), 0) Then .Cells(i, 1).EntireRow.Copy Destination:=Worksheets("Sheet7").Cells(j,1) -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch
Patrick Molloy wrote:
Application.Match often used "shortcut" for worksheet functions eg Application.VLookUp instead of Application.WorksheetFunction.VLookUp NOT SAFE. Always use the full syntax. Patrick, Can you elaborate on that. I never use WorksheetFunction so I can trap the errors similar to what Dave did later in this thread. I always thought is was a personal preference, but I'd be interested to know if there's something I'm not considering. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch
One thing that both you and I miss is the intellisense that you get with
..worksheetfunction, but I'm willing to live with that loss. Dick Kusleika wrote: Patrick Molloy wrote: Application.Match often used "shortcut" for worksheet functions eg Application.VLookUp instead of Application.WorksheetFunction.VLookUp NOT SAFE. Always use the full syntax. Patrick, Can you elaborate on that. I never use WorksheetFunction so I can trap the errors similar to what Dave did later in this thread. I always thought is was a personal preference, but I'd be interested to know if there's something I'm not considering. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch
I still get it. I use WorksheetFunction when writing and then delete it.
The only thing I don't like is trying to figure out where I want to break long lines. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Dave Peterson wrote: One thing that both you and I miss is the intellisense that you get with .worksheetfunction, but I'm willing to live with that loss. Dick Kusleika wrote: Patrick Molloy wrote: Application.Match often used "shortcut" for worksheet functions eg Application.VLookUp instead of Application.WorksheetFunction.VLookUp NOT SAFE. Always use the full syntax. Patrick, Can you elaborate on that. I never use WorksheetFunction so I can trap the errors similar to what Dave did later in this thread. I always thought is was a personal preference, but I'd be interested to know if there's something I'm not considering. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch
the idea of using a "safe" function is to use the on error method to trap
errors from functions like VLOOKUP and MATCH that raise errors when they fail to find th elookup item and this then means that your usual error handling remains much cleaner...IF you have them :) "Dick Kusleika" wrote in message ... I still get it. I use WorksheetFunction when writing and then delete it. The only thing I don't like is trying to figure out where I want to break long lines. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Dave Peterson wrote: One thing that both you and I miss is the intellisense that you get with .worksheetfunction, but I'm willing to live with that loss. Dick Kusleika wrote: Patrick Molloy wrote: Application.Match often used "shortcut" for worksheet functions eg Application.VLookUp instead of Application.WorksheetFunction.VLookUp NOT SAFE. Always use the full syntax. Patrick, Can you elaborate on that. I never use WorksheetFunction so I can trap the errors similar to what Dave did later in this thread. I always thought is was a personal preference, but I'd be interested to know if there's something I'm not considering. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Type Mismatch | Excel Worksheet Functions | |||
Type Mismatch | Excel Programming | |||
Type mismatch | Excel Programming | |||
Type Mismatch | Excel Programming |