|
Issue with For loop
Hi again,
One more question about my code, I promise. I just added in a For loop to go through a whole column of cells and I have a few issues with it. One, is the x in For Each x... going to translate into the .Find(What:=x,...) ? Two, the error is coming up in the Else...Next. It is saying that the Next doesn't have a For loop. What I want it to do is if it doesn't find a match to exit the For loop and go to the next cell in the B column. I thought that having a Next here and a next at the end of the code when it does find a match would work. Any suggestions? For Each x In Worksheets("Sheet5").Range("B1:B360").Cells Windows("VOD Master List as of 06-19-07.xls").Activate Set c = Worksheets("Master VOD List").Range("G1:G4633").Find(What:=x, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) If Not c Is Nothing Then c.Activate Else Next 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 Next End Sub |
Issue with For loop
Give this a try...
For Each x In Worksheets("Sheet5").Range("B1:B360").Cells Windows("VOD Master List as of 06-19-07.xls").Activate Set c = Worksheets("Master VOD List").Range("G1:G4633").Find(What:=x, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) If Not c Is Nothing Then c.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 End If Next End Sub -- HTH... Jim Thomlinson " wrote: Hi again, One more question about my code, I promise. I just added in a For loop to go through a whole column of cells and I have a few issues with it. One, is the x in For Each x... going to translate into the .Find(What:=x,...) ? Two, the error is coming up in the Else...Next. It is saying that the Next doesn't have a For loop. What I want it to do is if it doesn't find a match to exit the For loop and go to the next cell in the B column. I thought that having a Next here and a next at the end of the code when it does find a match would work. Any suggestions? For Each x In Worksheets("Sheet5").Range("B1:B360").Cells Windows("VOD Master List as of 06-19-07.xls").Activate Set c = Worksheets("Master VOD List").Range("G1:G4633").Find(What:=x, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) If Not c Is Nothing Then c.Activate Else Next 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 Next End Sub |
Issue with For loop
Thanks, works perfectly.
On Jul 9, 12:14 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: Give this a try... For Each x In Worksheets("Sheet5").Range("B1:B360").Cells Windows("VOD Master List as of 06-19-07.xls").Activate Set c = Worksheets("Master VOD List").Range("G1:G4633").Find(What:=x, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) If Not c Is Nothing Then c.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 End If Next End Sub -- HTH... Jim Thomlinson " wrote: Hi again, One more question about my code, I promise. I just added in a For loop to go through a whole column of cells and I have a few issues with it. One, is the x in For Each x... going to translate into the .Find(What:=x,...) ? Two, the error is coming up in the Else...Next. It is saying that the Next doesn't have a For loop. What I want it to do is if it doesn't find a match to exit the For loop and go to the next cell in the B column. I thought that having a Next here and a next at the end of the code when it does find a match would work. Any suggestions? For Each x In Worksheets("Sheet5").Range("B1:B360").Cells Windows("VOD Master List as of 06-19-07.xls").Activate Set c = Worksheets("Master VOD List").Range("G1:G4633").Find(What:=x, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) If Not c Is Nothing Then c.Activate Else Next 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 Next End Sub- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 05:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com