Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Searching certain ranges and stopping when finding no match
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default Searching certain ranges and stopping when finding no match
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Searching certain ranges and stopping when finding no match
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



" wrote:

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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 107
Default Searching certain ranges and stopping when finding no match
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 ...
--
p45cal


"Joel" wrote:

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



" wrote:

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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default Searching certain ranges and stopping when finding no match
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

On Jul 5, 12:42 pm, p45cal wrote:
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 ...
--
p45cal



"Joel" wrote:
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

" wrote:

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- Hide quoted text -

- Show quoted text -


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 107
Default Searching certain ranges and stopping when finding no match
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

--
p45cal


" wrote:

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

On Jul 5, 12:42 pm, p45cal wrote:
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 ...
--
p45cal


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
need to return next match of table, only finding first match...HEL nwtf_vol[_2_] Excel Worksheet Functions 0 January 23rd 08 01:42 AM
Searching and finding Titanus Excel Worksheet Functions 1 January 29th 07 06:56 PM
Searching, matching then searching another list based on the match A.S. Excel Discussion (Misc queries) 1 December 13th 06 05:08 AM
Searching an array and finding sum Tre_cool Excel Programming 5 September 21st 05 07:40 PM
searching a list box for a filename match...and highlighting the match suee[_4_] Excel Programming 1 April 13th 04 02:56 AM


All times are GMT +1. The time now is 12:57 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement