Searching certain ranges and stopping when finding no match

[nectarinesupreme]

Hi again,

I have a few questions dealing with the code that I've gotten so far.
It works in finding an item in one sheet and copying it into the other
sheet like I want it to. There are a few things that I need help
fixing. One, I need to stop the code when it doesn't find a match -
currently it's just coming up with an error. Two, I need it to search
a specific area (specifically G1:G5000) of the spreadsheet for the
match - currently it's searching the whole spreadsheet.

Thanks in advance!

Selection.Copy
x = ActiveCell
Windows("VOD Master List as of 06-19-07.xls").Activate
Cells.Find(What:=x, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
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

 

[Joel]

Selection.Copy
set x = range("G1:G5000")l
Windows("VOD Master List as of 06-19-07.xls").Activate
set c = Cells.Find(What:=x, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
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

 

[p45cal]

I suspect that

set x = range("G1:G5000")l
Windows("VOD Master List as of 06-19-07.xls").Activate
set c = Cells.Find(What:=x, After:=ActiveCell, LookIn:=xlFormulas...

should be

set x = activecell
Windows("VOD Master List as of 06-19-07.xls").Activate
set c = range("G1:G5000").Find(What:=x, After:=ActiveCell,
LookIn:=xlFormulas ...

 

[nectarinesupreme]

I think I added what both of you have said, and I'm getting a type
mismatch error on the Set c = ... line. Any suggestions? (Thanks again
for the help everyone, I really appreciate it.)

Selection.Copy
x = ActiveCell
Windows("VOD Master List as of 06-19-07.xls").Activate
Set c = Range("G1:G4").Find(What:=x, After:=ActiveCell,
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

 

[p45cal]

I'm sorry I didn't check all the code. Remove:

..Activate from the "set c = " line

and

After:=ActiveCell,

because the active cell isn't in the range.


This shoul leave you with something like this:

x = ActiveCell
Set c = Range("G1:G7").Find(What:=x, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If Not c Is Nothing Then
c.Activate
Else
Exit Sub
End If