Alphabetic Series...

[muhammad susanto]

hello all..

how to make this below code it works :

[COLOR=#333333]=char(code(e11)+1)[/COLOR]

a cross post
https://www.excelforum.com/excel-fo...fill-alphabetic-series-with-interval-row.html

any helps is greatly appreciated.

m.susanto

 

[Scott Huish]

Re: Alpabetic Series...

I wasn't able to download your spreadsheet from that other forum because I am not a member there. It does seem that you want to start with a letter then increment.
If the starting letter is different each time, then how many letters do you want to go down, until it reaches z or a certain number or what?

For this example, I am going to start in e11 and increment until it gets to z or Z if the starting letter was capital.

Sub test()
Dim x As Long, y As Long, startloop As Long, r As Long
startloop = Asc(Range("E11"))
If startloop > 90 Then y = 122 Else y = 90
r = 11
For x = startloop To y
    Cells(r, "E") = Chr(x)
    r = r + 1
Next
End Sub

 

[Scott Huish]

Re: Alpabetic Series...

As a formula, your formula is correct, but you do need to start with a letter in E11:

Excel 2010
	E
11	J
12	K
13	L
14	M
Sheet1

Cell Formulas
Range	Formula
E12	=CHAR(CODE(E11)+1)
E13	=CHAR(CODE(E12)+1)
E14	=CHAR(CODE(E13)+1)

 

[muhammad susanto]

Re: Alpabetic Series...

hi Scott Huish, thank you!! Worked it.