|
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 |
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 |
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 |
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 - |
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 |
| All times are GMT +1. The time now is 05:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com