Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > complex looping problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 19
Default complex looping problem
Is there a way to have this loop 25 times.

That is these three values would increment once each time though the loop:
Weeks1(), x = 1, and worksheets(1)
Weeks2(), x = 2, and worksheets(2)

Weeks3(), x = 3, and worksheets(3)

Weeks4(), x = 4, and worksheets(4)

Weeks5(), x = 5, and worksheets(5)



Sub Weeks1()
Dim r As Long 'this points to the worksheet row
Dim c As Long 'this points to the array column, not the worksheet column
Dim x As Integer
x = 1
Dim Sheet1Values As Variant
Worksheets(1).Activate


Sheet1Values = Worksheets(1).Range(Cells(x, 2), Cells(x, 8)).Resize(1,
7).Value
r = 7
c = 1
WeekNum r, c, 13, Sheet1Values


Sheet1Values = Worksheets(1).Range(Cells(x, 9), Cells(x, 15)).Resize(1,
7).Value
r = 17
c = 1

WeekNum r, c, 23, Sheet1Values

End Sub

Sub WeekNum(r As Long, c As Long, maxR As Long, sheetValues)
Dim rng As Range
With Worksheets(1)
Do While r <= maxR
Set rng = .Cells(r, 3).Resize(1, 5)
Select Case sheetValues(1, c)
Case 0
rng.Value = Array(0, 0, 0, "RDO", 0)
Case "a", "e"
rng.Value = Array("9:00", "17:21", "0:45", 0, 0)
End Select
r = r + 1
c = c + 1
Loop
End With
End Sub




Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,236
Default complex looping problem
Moving x up to be an argument:

Not sure if this is exactly what you want though...


Sub test()
Weeks 1
Weeks 2
Weeks 3
Weeks 4
Weeks 5
End Sub


Sub Weeks(x As Long)
Dim r As Long 'this points to the worksheet row
Dim c As Long 'this points to the array column, not the worksheet column

Dim SheetValues As Variant
Worksheets(x).Activate


SheetValues = Worksheets(x).Range(Cells(x, 2), Cells(x, 8)).Resize(1,
7).Value
r = 7
c = 1
WeekNum r, c, 13, SheetValues


SheetValues = Worksheets(x).Range(Cells(x, 9), Cells(x, 15)).Resize(1,
7).Value
r = 17
c = 1

WeekNum r, c, 23, SheetValues

End Sub



--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Max Bialystock" wrote in message
...
Is there a way to have this loop 25 times.

That is these three values would increment once each time though the
loop:
Weeks1(), x = 1, and worksheets(1)
Weeks2(), x = 2, and worksheets(2)

Weeks3(), x = 3, and worksheets(3)

Weeks4(), x = 4, and worksheets(4)

Weeks5(), x = 5, and worksheets(5)



Sub Weeks1()
Dim r As Long 'this points to the worksheet row
Dim c As Long 'this points to the array column, not the worksheet
column
Dim x As Integer
x = 1
Dim Sheet1Values As Variant
Worksheets(1).Activate


Sheet1Values = Worksheets(1).Range(Cells(x, 2), Cells(x, 8)).Resize(1,
7).Value
r = 7
c = 1
WeekNum r, c, 13, Sheet1Values


Sheet1Values = Worksheets(1).Range(Cells(x, 9), Cells(x,
15)).Resize(1,
7).Value
r = 17
c = 1

WeekNum r, c, 23, Sheet1Values

End Sub

Sub WeekNum(r As Long, c As Long, maxR As Long, sheetValues)
Dim rng As Range
With Worksheets(1)
Do While r <= maxR
Set rng = .Cells(r, 3).Resize(1, 5)
Select Case sheetValues(1, c)
Case 0
rng.Value = Array(0, 0, 0, "RDO", 0)
Case "a", "e"
rng.Value = Array("9:00", "17:21", "0:45", 0, 0)
End Select
r = r + 1
c = c + 1
Loop
End With
End Sub






Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default complex looping problem
Sub test()
For Each sh In
Worksheets(Array("Sheet1","Sheet2","Sheet3","Sheet 4","Sheet5")
Weeks 1,sh
Weeks 2,sh
Weeks 3,sh
Weeks 4,sh
Weeks 5,sh
End Sub


Sub Weeks(x As Long, sh As Worksheet)
Dim r As Long 'this points to the worksheet row
Dim c As Long 'this points to the array column, not the worksheet column
Dim SheetValues As Variant

With sh
SheetValues = Range(Cells(x, 2), Cells(x, 8)). _
Resize(1,7).Value
WeekNum 7,1, 13, SheetValues
SheetValues = .Range(Cells(x, 9), Cells(x, 15)). _
Resize(1,7).Value

WeekNum 17,1, 23, SheetValues
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Max Bialystock" wrote in message
...
Is there a way to have this loop 25 times.

That is these three values would increment once each time though the
loop:
Weeks1(), x = 1, and worksheets(1)
Weeks2(), x = 2, and worksheets(2)

Weeks3(), x = 3, and worksheets(3)

Weeks4(), x = 4, and worksheets(4)

Weeks5(), x = 5, and worksheets(5)



Sub Weeks1()
Dim r As Long 'this points to the worksheet row
Dim c As Long 'this points to the array column, not the worksheet
column
Dim x As Integer
x = 1
Dim Sheet1Values As Variant
Worksheets(1).Activate


Sheet1Values = Worksheets(1).Range(Cells(x, 2), Cells(x, 8)).Resize(1,
7).Value
r = 7
c = 1
WeekNum r, c, 13, Sheet1Values


Sheet1Values = Worksheets(1).Range(Cells(x, 9), Cells(x,
15)).Resize(1,
7).Value
r = 17
c = 1

WeekNum r, c, 23, Sheet1Values

End Sub

Sub WeekNum(r As Long, c As Long, maxR As Long, sheetValues)
Dim rng As Range
With Worksheets(1)
Do While r <= maxR
Set rng = .Cells(r, 3).Resize(1, 5)
Select Case sheetValues(1, c)
Case 0
rng.Value = Array(0, 0, 0, "RDO", 0)
Case "a", "e"
rng.Value = Array("9:00", "17:21", "0:45", 0, 0)
End Select
r = r + 1
c = c + 1
Loop
End With
End Sub






Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,236
Default complex looping problem
Syntax fixed

Sub test()
For Each sh In Worksheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4",
"Sheet5"))
Weeks 1, sh
Weeks 2, sh
Weeks 3, sh
Weeks 4, sh
Weeks 5, sh
Next sh
End Sub

I've aircoded too :)

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Bob Phillips" wrote in message
...
Sub test()
For Each sh In
Worksheets(Array("Sheet1","Sheet2","Sheet3","Sheet 4","Sheet5")
Weeks 1,sh
Weeks 2,sh
Weeks 3,sh
Weeks 4,sh
Weeks 5,sh
End Sub


Sub Weeks(x As Long, sh As Worksheet)
Dim r As Long 'this points to the worksheet row
Dim c As Long 'this points to the array column, not the worksheet
column
Dim SheetValues As Variant

With sh
SheetValues = Range(Cells(x, 2), Cells(x, 8)). _
Resize(1,7).Value
WeekNum 7,1, 13, SheetValues
SheetValues = .Range(Cells(x, 9), Cells(x, 15)). _
Resize(1,7).Value

WeekNum 17,1, 23, SheetValues
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Max Bialystock" wrote in message
...
Is there a way to have this loop 25 times.

That is these three values would increment once each time though the
loop:
Weeks1(), x = 1, and worksheets(1)
Weeks2(), x = 2, and worksheets(2)

Weeks3(), x = 3, and worksheets(3)

Weeks4(), x = 4, and worksheets(4)

Weeks5(), x = 5, and worksheets(5)



Sub Weeks1()
Dim r As Long 'this points to the worksheet row
Dim c As Long 'this points to the array column, not the worksheet
column
Dim x As Integer
x = 1
Dim Sheet1Values As Variant
Worksheets(1).Activate


Sheet1Values = Worksheets(1).Range(Cells(x, 2), Cells(x,
8)).Resize(1,
7).Value
r = 7
c = 1
WeekNum r, c, 13, Sheet1Values


Sheet1Values = Worksheets(1).Range(Cells(x, 9), Cells(x,
15)).Resize(1,
7).Value
r = 17
c = 1

WeekNum r, c, 23, Sheet1Values

End Sub

Sub WeekNum(r As Long, c As Long, maxR As Long, sheetValues)
Dim rng As Range
With Worksheets(1)
Do While r <= maxR
Set rng = .Cells(r, 3).Resize(1, 5)
Select Case sheetValues(1, c)
Case 0
rng.Value = Array(0, 0, 0, "RDO", 0)
Case "a", "e"
rng.Value = Array("9:00", "17:21", "0:45", 0, 0)
End Select
r = r + 1
c = c + 1
Loop
End With
End Sub








Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 19
Default complex looping problem
Gentlemen I thank you sincerely for your help.
But how do I keep this bit in synchronisation with the other bits?



Sub WeekNum(r As Long, c As Long, maxR As Long, SheetValues)
Dim rng As Range
With Worksheets(?)

Do While r <= maxR

Set rng = .Cells(r, 3).Resize(1, 5)
Select Case SheetValues(1, c)

Case 0
rng.Value = Array(0, 0, 0, "RDO", 0)
Case "a", "e"
rng.Value = Array("9:00", "17:21", "0:45", 0, 0)

End Select

r = r + 1
c = c + 1

Loop
End With

End Sub

With Worksheets(?) has to have the same value as each of the sheets in the
array shown below.

Sub test()
Worksheets(1).Activate
Dim sh As Worksheet
For Each sh In Worksheets(Array("4", "5", "6", "7", "8"))
Weeks 4, sh
Weeks 5, sh
Weeks 6, sh
Weeks 7, sh
Weeks 8, sh
Next sh
End Sub

--
Regards,
Max


"Rob van Gelder" wrote in message
...
Syntax fixed

Sub test()
For Each sh In Worksheets(Array("Sheet1", "Sheet2", "Sheet3",
"Sheet4",
"Sheet5"))
Weeks 1, sh
Weeks 2, sh
Weeks 3, sh
Weeks 4, sh
Weeks 5, sh
Next sh
End Sub

I've aircoded too :)

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Bob Phillips" wrote in message
...
Sub test()
For Each sh In
Worksheets(Array("Sheet1","Sheet2","Sheet3","Sheet 4","Sheet5")
Weeks 1,sh
Weeks 2,sh
Weeks 3,sh
Weeks 4,sh
Weeks 5,sh
End Sub


Sub Weeks(x As Long, sh As Worksheet)
Dim r As Long 'this points to the worksheet row
Dim c As Long 'this points to the array column, not the worksheet
column
Dim SheetValues As Variant

With sh
SheetValues = Range(Cells(x, 2), Cells(x, 8)). _
Resize(1,7).Value
WeekNum 7,1, 13, SheetValues
SheetValues = .Range(Cells(x, 9), Cells(x, 15)). _
Resize(1,7).Value

WeekNum 17,1, 23, SheetValues
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Max Bialystock" wrote in message
...
Is there a way to have this loop 25 times.

That is these three values would increment once each time though the
loop:
Weeks1(), x = 1, and worksheets(1)
Weeks2(), x = 2, and worksheets(2)

Weeks3(), x = 3, and worksheets(3)

Weeks4(), x = 4, and worksheets(4)

Weeks5(), x = 5, and worksheets(5)



Sub Weeks1()
Dim r As Long 'this points to the worksheet row
Dim c As Long 'this points to the array column, not the worksheet
column
Dim x As Integer
x = 1
Dim Sheet1Values As Variant
Worksheets(1).Activate


Sheet1Values = Worksheets(1).Range(Cells(x, 2), Cells(x,
8)).Resize(1,
7).Value
r = 7
c = 1
WeekNum r, c, 13, Sheet1Values


Sheet1Values = Worksheets(1).Range(Cells(x, 9), Cells(x,
15)).Resize(1,
7).Value
r = 17
c = 1

WeekNum r, c, 23, Sheet1Values

End Sub

Sub WeekNum(r As Long, c As Long, maxR As Long, sheetValues)
Dim rng As Range
With Worksheets(1)
Do While r <= maxR
Set rng = .Cells(r, 3).Resize(1, 5)
Select Case sheetValues(1, c)
Case 0
rng.Value = Array(0, 0, 0, "RDO", 0)
Case "a", "e"
rng.Value = Array("9:00", "17:21", "0:45", 0, 0)
End Select
r = r + 1
c = c + 1
Loop
End With
End Sub










Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default complex looping problem
What exactly does that question mean?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Max Bialystock" wrote in message
...
Gentlemen I thank you sincerely for your help.
But how do I keep this bit in synchronisation with the other bits?



Sub WeekNum(r As Long, c As Long, maxR As Long, SheetValues)
Dim rng As Range
With Worksheets(?)

Do While r <= maxR

Set rng = .Cells(r, 3).Resize(1, 5)
Select Case SheetValues(1, c)

Case 0
rng.Value = Array(0, 0, 0, "RDO", 0)
Case "a", "e"
rng.Value = Array("9:00", "17:21", "0:45", 0, 0)

End Select

r = r + 1
c = c + 1

Loop
End With

End Sub

With Worksheets(?) has to have the same value as each of the sheets in the
array shown below.

Sub test()
Worksheets(1).Activate
Dim sh As Worksheet
For Each sh In Worksheets(Array("4", "5", "6", "7", "8"))
Weeks 4, sh
Weeks 5, sh
Weeks 6, sh
Weeks 7, sh
Weeks 8, sh
Next sh
End Sub

--
Regards,
Max


"Rob van Gelder" wrote in message
...
Syntax fixed

Sub test()
For Each sh In Worksheets(Array("Sheet1", "Sheet2", "Sheet3",
"Sheet4",
"Sheet5"))
Weeks 1, sh
Weeks 2, sh
Weeks 3, sh
Weeks 4, sh
Weeks 5, sh
Next sh
End Sub

I've aircoded too :)

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Bob Phillips" wrote in message
...
Sub test()
For Each sh In
Worksheets(Array("Sheet1","Sheet2","Sheet3","Sheet 4","Sheet5")
Weeks 1,sh
Weeks 2,sh
Weeks 3,sh
Weeks 4,sh
Weeks 5,sh
End Sub


Sub Weeks(x As Long, sh As Worksheet)
Dim r As Long 'this points to the worksheet row
Dim c As Long 'this points to the array column, not the worksheet
column
Dim SheetValues As Variant

With sh
SheetValues = Range(Cells(x, 2), Cells(x, 8)). _
Resize(1,7).Value
WeekNum 7,1, 13, SheetValues
SheetValues = .Range(Cells(x, 9), Cells(x, 15)). _
Resize(1,7).Value

WeekNum 17,1, 23, SheetValues
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Max Bialystock" wrote in message
...
Is there a way to have this loop 25 times.

That is these three values would increment once each time though
the
loop:
Weeks1(), x = 1, and worksheets(1)
Weeks2(), x = 2, and worksheets(2)

Weeks3(), x = 3, and worksheets(3)

Weeks4(), x = 4, and worksheets(4)

Weeks5(), x = 5, and worksheets(5)



Sub Weeks1()
Dim r As Long 'this points to the worksheet row
Dim c As Long 'this points to the array column, not the
worksheet
column
Dim x As Integer
x = 1
Dim Sheet1Values As Variant
Worksheets(1).Activate


Sheet1Values = Worksheets(1).Range(Cells(x, 2), Cells(x,
8)).Resize(1,
7).Value
r = 7
c = 1
WeekNum r, c, 13, Sheet1Values


Sheet1Values = Worksheets(1).Range(Cells(x, 9), Cells(x,
15)).Resize(1,
7).Value
r = 17
c = 1

WeekNum r, c, 23, Sheet1Values

End Sub

Sub WeekNum(r As Long, c As Long, maxR As Long, sheetValues)
Dim rng As Range
With Worksheets(1)
Do While r <= maxR
Set rng = .Cells(r, 3).Resize(1, 5)
Select Case sheetValues(1, c)
Case 0
rng.Value = Array(0, 0, 0, "RDO", 0)
Case "a", "e"
rng.Value = Array("9:00", "17:21", "0:45", 0, 0)
End Select
r = r + 1
c = c + 1
Loop
End With
End Sub












Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 19
Default complex looping problem
In
---
Sub WeekNum(r As Long, c As Long, maxR As Long, SheetValues)
With Worksheets(4)
---
what do I put in place of the 4 in worksheets(4)?



Sub test()
Worksheets(1).Activate
Dim sh As Worksheet
For Each sh In Worksheets(Array("4", "5", "6", "7", "8"))
Weeks 4, sh
Weeks 5, sh
Weeks 6, sh
Weeks 7, sh
Weeks 8, sh
Next sh
End Sub
Sub Weeks(x As Long, sh As Worksheet)
Dim r As Long 'this points to the worksheet row
Dim c As Long 'this points to the array column, not the worksheet column
Dim SheetValues As Variant

With sh
SheetValues = Range(Cells(x, 2), Cells(x, 8)).Resize(1, 7).Value
WeekNum 7, 1, 13, SheetValues
SheetValues = Range(Cells(x, 9), Cells(x, 15)).Resize(1, 7).Value
WeekNum 17, 1, 23, SheetValues
End With
End Sub
Sub WeekNum(r As Long, c As Long, maxR As Long, SheetValues)
With Worksheets(4)

Do While r <= maxR

Set rng = .Cells(r, 3).Resize(1, 5)
Select Case SheetValues(1, c)

Case 0
rng.Value = Array(0, 0, 0, "RDO", 0)
Case "a", "e"
rng.Value = Array("9:00", "17:21", "0:45", 0, 0)

End Select

r = r + 1
c = c + 1

Loop
End With

End Sub


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default complex looping problem
Thanks Rob!

Bob

"Rob van Gelder" wrote in message
...
Syntax fixed

Sub test()
For Each sh In Worksheets(Array("Sheet1", "Sheet2", "Sheet3",
"Sheet4",
"Sheet5"))
Weeks 1, sh
Weeks 2, sh
Weeks 3, sh
Weeks 4, sh
Weeks 5, sh
Next sh
End Sub

I've aircoded too :)

--
Rob van Gelder - http://www.vangelder.co.nz/excel



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
Scrolling Looping Problem Nick Wakeham Excel Discussion (Misc queries) 0 June 12th 07 01:42 PM
Macro looping problem. [email protected] Excel Discussion (Misc queries) 8 October 26th 06 02:44 PM
Looping Problem Todd Huttenstine[_3_] Excel Programming 5 January 25th 04 12:51 AM
looping formula - r1c1 problem - Con't MDC[_2_] Excel Programming 0 October 22nd 03 11:42 PM
looping formula - r1c1 problem MDC[_2_] Excel Programming 2 October 22nd 03 11:13 PM


All times are GMT +1. The time now is 09:58 AM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement