Loop

[Neil]

Hi,

Is there some way to put a number sequence in the following pattern
into a sheet..
A B C D E F
Row 1 a
Row 2 a b
Row 3 a b c
Row 4 a b c d
Row 5 a b c d e
Row 6 a b c d e f


Thanks

Neil

 

[joel]

Sub writeLeters()

Letter_A = Asc("a")
For i = 1 To 260
LetterNumber = (i - 1) Mod 26
For j = 1 To (LetterNumber + 1)
NewLetter = Chr(Letter_A + (j - 1))
Cells(i, j) = NewLetter
Next j
Next i

End Su

 

[RB Smissaert]

Sub FillUp(lMax As Long)

Dim r As Long
Dim c As Long

For r = 1 To lMax
For c = 1 To r
Cells(r, c) = Chr(96 + c)
Next c
Next r

End Sub


Sub test()

FillUp 6

End Sub


RBS

 

[Neil]

Thanks RB & Joel!

I'm trying to understand how this loop works..could you explain
please?

thanks again!

Neil

 

[RB Smissaert]

Look in the help at For Next loops.
There is an outer loop (r counter), looping through rows and an
inner loop (c counter), looping through columns.
You could add a Msgbox before the cells line:
Msgbox "now doing row " & r & vbcrlf & "column " & c
to make it clearer what is going on.
A basic book about VB(A) would help.

RBS


Thanks RB & Joel!

I'm trying to understand how this loop works..could you explain
please?

thanks again!

Neil

 

[joel]

Each character has a ASCII code asociated with each character. Th
lower case letters a-z start at character 96

Characternumber = asc("a") will equal 96


The letter b is 97 which is asc("a") + 1
The letter c is 98 which is asc("a") + 2


to convert a number back to the letter use the method Chr()

So MyLetterA = chr(96) which is lowercase "a".


the letter b equals chr(asc("a") + 1)
the letter c equals chr(asc("a") + 2)
the letter d equals chr(asc("a") + 3

 

[RB Smissaert]

Except that "a" is Chr(97):
MsgBox Asc("a") , , Chr(97)

RBS

 

[joel]

The is why I used chr("a") rather than the number 96. I rember the he
equivalent that digits are 30 to 39, capital A is 41, lower case a is 6
(6 x 16 + 1 = 96). I was looking at Neil's code where here used th
numbers and used 96 + 1 to get the letter "a". I didn't look closel
that he started his for loop at 1 where I started at 0.

Basic originally started arrays and indexing at one at later added th
feature of having the option for either zero or one. I'm used to
Language which always used zero as the first item in arrays. This is
problem that has been happening for over 2000 years. Man invented on
before he invented zero (and negative numbers)