Is there a function that converts integers (as used in programming
loops) to excel colums letters?

For instance:

Worksheet.Cells(1,1).Formula = Sum(HypotheticalFunc(2) & "1:" &
HypotheticalFunc(26) & "1")

Should place the Formula "=Sum("B1:Z1") into the cell A1

If not, I'll write it, but it seems as if it should exist.

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function



--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"kramer31" wrote in message
oups.com...
Is there a function that converts integers (as used in programming
loops) to excel colums letters?

For instance:

Worksheet.Cells(1,1).Formula = Sum(HypotheticalFunc(2) & "1:" &
HypotheticalFunc(26) & "1")

Should place the Formula "=Sum("B1:Z1") into the cell A1

If not, I'll write it, but it seems as if it should exist.

On 13 Oct 2006 14:48:25 -0700, "kramer31" wrote:

Is there a function that converts integers (as used in programming
loops) to excel colums letters?

For instance:

Worksheet.Cells(1,1).Formula = Sum(HypotheticalFunc(2) & "1:" &
HypotheticalFunc(26) & "1")

Should place the Formula "=Sum("B1:Z1") into the cell A1

If not, I'll write it, but it seems as if it should exist.

Assuming your worksheet is NOT set to use RC notation,

Worksheets("Sheet1").Cells(1, 1).Formula = "=SUM(R1C2:R1C26)"

should translate just fine.


--ron

Why not just use .formular1c1?

Worksheets("Sheet1").Cells(1, 1).FormulaR1C1 = "=SUM(R1C2:R1C26)"

Even though .formula worked for me (xl2003) in both R1C1 and A1 reference style,
..formula looks like a problem just waiting to happen.



Ron Rosenfeld wrote:

On 13 Oct 2006 14:48:25 -0700, "kramer31" wrote:

Is there a function that converts integers (as used in programming
loops) to excel colums letters?

For instance:

Worksheet.Cells(1,1).Formula = Sum(HypotheticalFunc(2) & "1:" &
HypotheticalFunc(26) & "1")

Should place the Formula "=Sum("B1:Z1") into the cell A1

If not, I'll write it, but it seems as if it should exist.

Assuming your worksheet is NOT set to use RC notation,

Worksheets("Sheet1").Cells(1, 1).Formula = "=SUM(R1C2:R1C26)"

should translate just fine.

--ron

--

Dave Peterson

On Fri, 13 Oct 2006 19:41:35 -0500, Dave Peterson
wrote:

Why not just use .formular1c1?

Worksheets("Sheet1").Cells(1, 1).FormulaR1C1 = "=SUM(R1C2:R1C26)"

Even though .formula worked for me (xl2003) in both R1C1 and A1 reference style,
.formula looks like a problem just waiting to happen.

Perhaps I misread something or am not understanding HELP correctly.

The OP wanted the result in the A1 style.

From VBA Help:

FormulaR1C1 Property: Returns or sets the formula for the object, using
R1C1-style notation ...

Formula Property: Returns or sets the object's formula in A1-style notation ...

So it seemed to me that the Formula property was the more appropriate, since we
wanted to SET the object's formula in A1-style notation.

I could sure be missing some nuance of VBA that isn't clearly outlined in HELP,
though.

???

--ron

..formular1c1 means that you (as a coder) will write the formula in R1C1
reference style.

Excel will use the tools|options|view to show it to the user in whatever style
is chosen.

The same thing can be used with .formula. If you create a formula in VBA using
..formula, then excel will show it to the user using that setting.





Ron Rosenfeld wrote:

On Fri, 13 Oct 2006 19:41:35 -0500, Dave Peterson
wrote:

Why not just use .formular1c1?

Worksheets("Sheet1").Cells(1, 1).FormulaR1C1 = "=SUM(R1C2:R1C26)"

Even though .formula worked for me (xl2003) in both R1C1 and A1 reference style,
.formula looks like a problem just waiting to happen.

Perhaps I misread something or am not understanding HELP correctly.

The OP wanted the result in the A1 style.

From VBA Help:

FormulaR1C1 Property: Returns or sets the formula for the object, using
R1C1-style notation ...

Formula Property: Returns or sets the object's formula in A1-style notation ...

So it seemed to me that the Formula property was the more appropriate, since we
wanted to SET the object's formula in A1-style notation.

I could sure be missing some nuance of VBA that isn't clearly outlined in HELP,
though.

???

--ron

--

Dave Peterson

On Fri, 13 Oct 2006 20:06:29 -0500, Dave Peterson
wrote:

.formular1c1 means that you (as a coder) will write the formula in R1C1
reference style.

Excel will use the tools|options|view to show it to the user in whatever style
is chosen.

The same thing can be used with .formula. If you create a formula in VBA using
.formula, then excel will show it to the user using that setting.

Hmm. That's different from how I'm reading the HELP file. But it wouldn't be
the first time. It's curious that when setting the range object, it doesn't
seem to matter -- what comes out depends on the worksheet setting.
--ron

One mo

With Worksheets("sheet1")
.Cells(1, 1).Formula _
= "=SUM(" & .Cells(2, 1).Address(0, 0) _
& ":" & .Cells(26, 1).Address(0, 0) & ")"
End With

And one more...

Dim myRng As Range
With Worksheets("sheet1")
Set myRng = .Range(.Cells(2, 1), .Cells(26, 1))
.Cells(1, 1).Formula = "=SUM(" & myRng.Address(0, 0) & ")"
End With

This lets excel worry about the syntax.


kramer31 wrote:

Is there a function that converts integers (as used in programming
loops) to excel colums letters?

For instance:

Worksheet.Cells(1,1).Formula = Sum(HypotheticalFunc(2) & "1:" &
HypotheticalFunc(26) & "1")

Should place the Formula "=Sum("B1:Z1") into the cell A1

If not, I'll write it, but it seems as if it should exist.

--

Dave Peterson