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)

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)

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)

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!

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!

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!

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

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

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

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

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