Excel needs function to Convert Rowindex into RowCode (2 - B)etc
 

something like:
Function Number2Row(Number As Long) As String
' 1 bis 256
Dim OrgValue As Long
Dim newValue As Long
Dim Rest As Long

OrgValue = Number

If Number <= 0 Then
result = ""
GoTo finish
End If

'Rest = Number Mod 26

counter = 0
Do While Number 26
Number = Number - 26
counter = counter + 1
Loop

'If counter = 0 Then counter = 1

newValue = (OrgValue - (counter * 26))

If counter = 0 Then 'einer
result = Chr$(Number + 64) & Number2Row(OrgValue - 26)
Else ' ab zweiter Stelle
result = Chr$(counter + 64) & Number2Row(OrgValue - (counter * 26))
End If

finish:
Number2Row = result
End Function


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

Excel needs function to Convert Rowindex into RowCode (2 - B)etc
 

Sounds like you are talking about a column label under A1 style addressing.
If so, you can just use

? Left(Cells(1,36).Address(0,0),1 - (3626))
AJ

where 36 could be replaced with a variable that holds the column number.

--
Regards,
Tom Ogilvy


"Markus" wrote:

something like:
Function Number2Row(Number As Long) As String
' 1 bis 256
Dim OrgValue As Long
Dim newValue As Long
Dim Rest As Long

OrgValue = Number

If Number <= 0 Then
result = ""
GoTo finish
End If

'Rest = Number Mod 26

counter = 0
Do While Number 26
Number = Number - 26
counter = counter + 1
Loop

'If counter = 0 Then counter = 1

newValue = (OrgValue - (counter * 26))

If counter = 0 Then 'einer
result = Chr$(Number + 64) & Number2Row(OrgValue - 26)
Else ' ab zweiter Stelle
result = Chr$(counter + 64) & Number2Row(OrgValue - (counter * 26))
End If

finish:
Number2Row = result
End Function


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

Excel needs function to Convert Rowindex into RowCode (2 - B)
 

so that instead of a full function will do it jobs
Thank you!

Left(Cells(1, ActiveCell.Column).Address(0, 0), 1 - (ActiveCell.Column 26))


"Tom Ogilvy" wrote:

Sounds like you are talking about a column label under A1 style addressing.
If so, you can just use

? Left(Cells(1,36).Address(0,0),1 - (3626))
AJ

where 36 could be replaced with a variable that holds the column number.

--
Regards,
Tom Ogilvy


"Markus" wrote:

something like:
Function Number2Row(Number As Long) As String
' 1 bis 256
Dim OrgValue As Long
Dim newValue As Long
Dim Rest As Long

OrgValue = Number

If Number <= 0 Then
result = ""
GoTo finish
End If

'Rest = Number Mod 26

counter = 0
Do While Number 26
Number = Number - 26
counter = counter + 1
Loop

'If counter = 0 Then counter = 1

newValue = (OrgValue - (counter * 26))

If counter = 0 Then 'einer
result = Chr$(Number + 64) & Number2Row(OrgValue - 26)
Else ' ab zweiter Stelle
result = Chr$(counter + 64) & Number2Row(OrgValue - (counter * 26))
End If

finish:
Number2Row = result
End Function


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

Excel needs function to Convert Rowindex into RowCode (2 - B)
 

would it be possible to add that as an example in the "help"-file for future
versions?


"Tom Ogilvy" wrote:

Sounds like you are talking about a column label under A1 style addressing.
If so, you can just use

? Left(Cells(1,36).Address(0,0),1 - (3626))
AJ

where 36 could be replaced with a variable that holds the column number.

--
Regards,
Tom Ogilvy


"Markus" wrote:

something like:
Function Number2Row(Number As Long) As String
' 1 bis 256
Dim OrgValue As Long
Dim newValue As Long
Dim Rest As Long

OrgValue = Number

If Number <= 0 Then
result = ""
GoTo finish
End If

'Rest = Number Mod 26

counter = 0
Do While Number 26
Number = Number - 26
counter = counter + 1
Loop

'If counter = 0 Then counter = 1

newValue = (OrgValue - (counter * 26))

If counter = 0 Then 'einer
result = Chr$(Number + 64) & Number2Row(OrgValue - 26)
Else ' ab zweiter Stelle
result = Chr$(counter + 64) & Number2Row(OrgValue - (counter * 26))
End If

finish:
Number2Row = result
End Function


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming