Type Mismatch

[Guest]

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)

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Sharad]

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

 

[Guest]

Application.Match

often used "shortcut" for worksheet functions
eg
Application.VLookUp
instead of Application.WorksheetFunction.VLookUp

NOT SAFE. Always use the full syntax.

 

[Sharad Naik]

Thanks Patrick,
I already guessed it from your earlier post.
Sharad

 

[Dave Peterson]

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

 

[Dick Kusleika]

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.

 

[Dave Peterson]

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]

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.

 

[Patrick Molloy]

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