Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Issue with For loop
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default 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 -


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
For next do loop issue Christian Falde Excel Discussion (Misc queries) 4 November 11th 08 07:01 PM
Naming Worksheets - Loop within a loop issue klysell Excel Programming 5 March 29th 07 05:48 AM
Naming Worksheets - Loop within a loop issue klysell Excel Programming 0 March 27th 07 11:17 PM
do ... until loop issue pjjclark Excel Programming 4 November 29th 05 04:24 PM
Issue with Do Loop Linking to specific cells in pivot table Excel Programming 3 July 24th 05 10:38 PM


All times are GMT +1. The time now is 01:52 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