Help with type mismatch error

[nectarinesupreme]

Hi all,

I've posted about this problem later last week, but I still haven't
been able to fix up my code. Thanks for all the help, I'm really not
too great figuring out the errors.

The issue is going to be in the Set c = Range... line up until the End
If line. All of the other code has previously worked until I added
this new part. Basically the code is trying to copy and paste certain
data from one spreadsheet to another in a specific location. This one
part is trying to find the line in the other spreadsheet where it
should copy - depending a match within the G column. If it doesn't
find a match, it isn't supposed to copy anywhere. I'm currently
getting a type mismatch error on the Set c = Range... line. I really
don't know VB syntax at all, so I'm not sure what's wrong. Thanks!


Selection.Copy
x = ActiveCell
Windows("VOD Master List as of 06-19-07.xls").Activate
Set c = Range("G1:G4633").Find(What:=x, LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
If Not c Is Nothing Then
c.Activate
Else
Exit Sub
End If
Windows("May as of 06-01-07.xls").Activate
datecomp = "A1"
cellshow = "B" & ActiveCell.Row
cellSTB = "H" & ActiveCell.Row
cellorder = "I" & ActiveCell.Row
testdate = "D" & ActiveCell.Row
Worksheets("Sheet5").Range(cellshow).Font.Italic = True
Worksheets("Sheet5").Range(cellshow).Font.Bold = True
Range(cellSTB).Copy
If (Range(testdate) > Range(datecomp)) Then
Windows("VOD Master List as of 06-19-07.xls").Activate
cellSTBpaste = "Y" & ActiveCell.Row
cellorderpaste = "X" & ActiveCell.Row
Else
Windows("VOD Master List as of 06-19-07.xls").Activate
cellSTBpaste = "AB" & ActiveCell.Row
cellorderpaste = "AA" & ActiveCell.Row
End If
Range(cellSTBpaste).PasteSpecial
Windows("May as of 06-01-07.xls").Activate
Range(cellorder).Copy
Windows("VOD Master List as of 06-19-07.xls").Activate
Range(cellorderpaste).PasteSpecial

 

[Tom Ogilvy]

Perhaps:

Dim rng as Range

x = ActiveCell
Windows("VOD Master List as of 06-19-07.xls").Activate
Set c = Range("G1:G4633").Find( _
What:=x, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows,_
SearchDirection:=xlNext, _
MatchCase:=False)
If Not c Is Nothing Then
Application.Goto c, True
Else
Exit Sub
End If
selection.Copy

 

[nectarinesupreme]

I believe this is what you wanted me to have. I tried both with/
without the SearchFormat:= False - I wasn't sure if you wanted me to
get rid of that. I'm still getting a typemismatch error on the Set
c... line. I did notice that it is finding the correct line within my
other spreadsheet before the error happens though.

When I'm running the code for something where it shouldn't find a
match, I'm getting an "Object variable or With block variable not set"
error.

Thanks!

Selection.Copy
Dim rng As Range
x = ActiveCell
Windows("VOD Master List as of 06-19-07.xls").Activate
Set c = Range("G1:G4633").Find(What:=x, LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
If Not c Is Nothing Then
Application.Goto c, True
Else
Exit Sub
End If

 

[nectarinesupreme]

Just noticed that you removed the .Activate. Makes sense now. Thanks!

 

[Tom Ogilvy]

Looks like you missed the whole point of taking activate off the end of the
command.