For Next with negative step ?

S

Slim Slender

Public Sub FiftyFifty1()
Dim x As Integer, y As Integer

With ActiveSheet
For y = 1 To 50
For x = 0 To y
.Cells(y, 1 + x).Value = y - x & "/" & x
Next x
Next y
End With
End Sub

The above bit of code generates a bunch of pairs of numbers in a wedge-
shaped pattern on a spreadsheet.
If you copy and run it you'll see the pattern.

What I want is to generate another wedge-shaped pattern of pairs of
numbers starting on line 51 and going to line 100, where line 51 has
50 elements: 50/1, 49/2, 48/3 . . . 3/48, 2/49, 1/50 (all pairs add up
to 51)
line 52 has 49 elements: 50/2, 49/3, 48/4 . . . 4/48, 3/49, 2/50. (all
pairs add up to 52)
Line 99 has 2 elements: 50/49, 49/50 (add up to 99)
Line 100 has only: 50/50.
 
C

Clif McIrvin

Not sure what your question is. Yes, negative step values are allowed ..
put your cursor on one of the "for" statements in your code and hit F1
to get help.

From that help text:

For counter = start To end [Step step]

Clif
 
G

GS

Slim Slender pretended :
What I want is to generate another wedge-shaped pattern of pairs of
numbers starting on line 51 and going to line 100, where line 51 has
50 elements: 50/1, 49/2, 48/3 . . . 3/48, 2/49, 1/50 (all pairs add up
to 51)
line 52 has 49 elements: 50/2, 49/3, 48/4 . . . 4/48, 3/49, 2/50. (all
pairs add up to 52)
Line 99 has 2 elements: 50/49, 49/50 (add up to 99)
Line 100 has only: 50/50.

If what you want is a mirror pattern of what your code sample gives,
AND using a different methodology to determine the values for each
cell, then you need to use incremental counters in your calcs. The
ideology you expect happens that each calc reflects the row number in
its sum of digits. This precludes that row position be part of the
calc. I modified your code sample to give the expected results as per
your explanation.

Public Sub FiftyFifty1()
Dim x As Long, y As Long, k As Long, lPos As Long
With ActiveSheet
For y = 1 To 50
For x = 0 To y
.Cells(y, 1 + x).Value = y - x & "/" & x
Next x
Next y
lPos = 51: k = 1
For y = 50 To 1 Step -1
For x = 0 To y - 1
.Cells(lPos, x + 1) = (lPos - (x + k)) & "/" & k + x
Next
lPos = lPos + 1: k = k + 1
Next
End With
End Sub

HTH
 
S

Slim Slender

Slim Slender pretended :


If what you want is a mirror pattern of what your code sample gives,
AND using a different methodology to determine the values for each
cell, then you need to use incremental counters in your calcs. The
ideology you expect happens that each calc reflects the row number in
its sum of digits. This precludes that row position be part of the
calc. I modified your code sample to give the expected results as per
your explanation.

Public Sub FiftyFifty1()
  Dim x As Long, y As Long, k As Long, lPos As Long
  With ActiveSheet
    For y = 1 To 50
      For x = 0 To y
        .Cells(y, 1 + x).Value = y - x & "/" & x
      Next x
    Next y
    lPos = 51: k = 1
    For y = 50 To 1 Step -1
      For x = 0 To y - 1
        .Cells(lPos, x + 1) = (lPos - (x + k)) & "/" & k + x
      Next
      lPos = lPos + 1: k = k + 1
    Next
  End With
End Sub

HTH

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

YOU NAILED IT! Thanks Garry. Now I just have to study to see how you
did it.
 
G

GS

Slim Slender laid this down:
YOU NAILED IT! Thanks Garry. Now I just have to study to see how you
did it.

You're welcome!

I guess some documentation would be helpful toward understanding how it
works, so I've inserted some comment...

'Initialize the incremental counters.
'lPos sets row position, and is used to calc the left side of result.
'k holds the value to calc both sides of the result.
lPos = 51: k = 1

'y is used to loop the rows to insert values into, and is used to set
the number of columns for each iteration of the loop that follows.
'This counts down from the start row to the end row of the pattern.
For y = 50 To 1 Step -1

'x is used to loop the columns to insert values into.
'This counts up from column1 to column y for each iteration;
'This is how the number of columns reduces for each row.
'This loop starts at zero so we must use y minus 1 to get 50 columns.
For x = 0 To y - 1

'The left side of the next statement sets row/col position.
'Each iteration shifts the target cell one column to the right.
'(Each iteration of the 'For y' loop reduces the number of columns)

'The right side of the next statement calcs the left/right values.

'The left value is the row number minus the sum of x and k.
'As lPos (row number) increases, it gets reduced to 50 by subtracting
the sum of x and k (which also increase for each iteration).

'The right side is the sum of x and k.
'k increases for each column of each row, and each row of the pattern.
.Cells(lPos, x + 1) = (lPos - (x + k)) & "/" & k + x
Next

'Both counters are incremented before starting the next row.
lPos = lPos + 1: k = k + 1
Next

HTH
 
S

Slim Slender

Slim Slender laid this down:


You're welcome!

I guess some documentation would be helpful toward understanding how it
works, so I've inserted some comment...

'Initialize the incremental counters.
'lPos sets row position, and is used to calc the left side of result.
'k holds the value to calc both sides of the result.
    lPos = 51: k = 1

'y is used to loop the rows to insert values into, and is used to set
the number of columns for each iteration of the loop that follows.
'This counts down from the start row to the end row of the pattern.
    For y = 50 To 1 Step -1

'x is used to loop the columns to insert values into.
'This counts up from column1 to column y for each iteration;
'This is how the number of columns reduces for each row.
'This loop starts at zero so we must use y minus 1 to get 50 columns.
      For x = 0 To y - 1

'The left side of the next statement sets row/col position.
'Each iteration shifts the target cell one column to the right.
'(Each iteration of the 'For y' loop reduces the number of columns)

'The right side of the next statement calcs the left/right values.

'The left value is the row number minus the sum of x and k.
'As lPos (row number) increases, it gets reduced to 50 by subtracting
the sum of x and k (which also increase for each iteration).

'The right side is the sum of x and k.
'k increases for each column of each row, and each row of the pattern.
        .Cells(lPos, x + 1) = (lPos - (x + k)) & "/" & k + x
      Next

'Both counters are incremented before starting the next row.
      lPos = lPos + 1: k = k + 1
    Next

HTH

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Explanation much appreciated. Thanks again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top