Fill A AZ

[Press]

Hello

A courtesy, any suggestions


Sub Serie_AZ()
Dim txt As Long
txt = 27
Dim strMyArray(1 To 26 * 27, 1 To 1) As String
iOffset = 0
serie = 0
For ciclo = 1 To txt
For i = 1 To 26
strMyArray(serie + i, 1) = K & Chr(Asc("A") + ((i - 1 + iOffset) Mod 26))
Next i
K = Chr(Asc("A") + ((ciclo - 1 + iOffset) Mod 26))
serie = serie + 26
Next
Range("F1:F" & UBound(strMyArray)) = strMyArray
End Sub


Gian

[Yongle]

How about?



Sub AtoZ()
For i = 1 To 702
Cells(i, 6).Value = Split(Cells(1, i).Address(1, 0), "$")(0)
Next i
End Sub

[Press]

Opps!

Ok. One line for the cycle

Cells(i, 6).Value = Split(Cells(1, i).Address(1, 0), "$")(0)

I need to test it in another code Test.
I would like if possible to avoid referring to the sheet (.Address)

[Yongle]

If you achieve that with my code, I would be amazed!

[Yongle]

Another possibilty

For i = 1 To 702
Cells(i, 6).Value = "=IF(ROW()<27,CHAR(ROW()+64),CHAR(INT((ROW()-1)/26)+64)&CHAR(MOD(ROW()-1,26)+65))"
Next i

[Press]

Hi,

My interpretation of the formula
The data in the array does not appear on the page, use them in a subsequent cycle


Sub Test_AZ()
Dim strMyArray(1 To 702, 1 To 1) As String
For i = 1 To UBound(strMyArray)
strMyArray(i, 1) = Evaluate("if(" & i & "<27,CHAR(" & i & "+64),CHAR(INT((" & i & "-1)/26)+64)&CHAR(MOD(" & i & "-1,26)+65))")
Next i
Range("H1:H" & UBound(strMyArray)) = strMyArray
End Sub


Thank suggestions

[Yongle]

Similar to your first method


Dim MyString As String
Dim i
Dim j
For j = 64 To 90
For i = 65 To 90
MyString = Chr(j) & Chr(i)
If j = 64 Then
MyString = Chr(i)
End If
r = r + 1
Cells(r, 6).Value = MyString
Next i
Next j

[Yongle]

And yet another twist
The end!!


Sub AtoZ4()
For i = 1 To 702
Cells(i, 7) = Evaluate("if(" & i & "<27,CHAR(" & i & "+64),CHAR(INT((" & i & "-1)/26)+64)&CHAR(MOD(" & i & "-1,26)+65))")
Next i
End Sub

[Press]

Thanks,Yongle (http://www.vbaexpress.com/forum/member.php?55974-Yongle)

I think I'm OK for now.

[snb]

I'd prefer:


Sub M_snb()
sn = [index(char(65+int((row(1:676)-1)/26)) & char(65+mod(row(1:676)-1,26)),)]
End Sub
or

Sub M_snb()
sn = [index(substitute(char(64+int((row(1:702)-1)/26)),"@","") & char(65+mod(row(1:702)-1,26)),)]
End Sub

[Yongle]

@snb When running your subs, nothing appears to happen. What is the magic dust to sprinkle so that the desired results are visible in the worksheet?

[snb]

Sub M_snb()
sheet1.cells(1).resize(702) = [index(substitute(char(64+int((row(1:702)-1)/26)),"@","") & char(65+mod(row(1:702)-1,26)),)]
End Sub

[Yongle]

@snb Is it possible to use your shorthand notation on this one to avoid the From 1 to 702 ... Next

Sub AtoZ()
For i = 1 To 702
Cells(i, 6).Value = Split(Cells(1, i).Address(1, 0), "$")(0)
Next i
End Sub




or the worksheet formula =(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1",""))

[snb]

Sub M_snb()
Sheet1.Cells(1).Resize(702) = [index(substitute(address(1,row(1:702),4),"1",""),)]
End Sub

[Yongle]

I vote for this one-liner, which, at only 82 characters, saves us the most keyboard time!!

Sub M_snb()
Sheet1.Cells(1).Resize(702) = [index(substitute(address(1,row(1:702),4),"1",""),)]
End Sub