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

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

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

But if you read help as a directive to you (the programmer) how to write the
formula in code, then maybe it makes more sense???

And when myRng.address(0,0) or
myrng.address(rowabsolute:=false,columnabsolute:=f alse) is used, then it relies
on the default reference style (A1) to be used. (In fact, the row/column stuff
won't affect the referencestyle argument. I only included them because I used
them in the sample code.)

If I wanted to use R1C1 Style, I'd have to be more explicit:
msgbox myrng.address(ReferenceStyle:=xlr1c1)

or
..Cells(1, 1).FormulaR1C1 = "=SUM(" & myRng.Address(referencestyle:=xlr1c1) & ")"


Ron Rosenfeld wrote:

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

--

Dave Peterson

On Fri, 13 Oct 2006 21:10:34 -0500, Dave Peterson
wrote:

But if you read help as a directive to you (the programmer) how to write the
formula in code, then maybe it makes more sense???

Yes, it does. I just never read it that way.
--ron

Sub WriteFormula()
ActiveCell.FormulaR1C1 = "=Sum(A1:A10)"
ActiveCell.Offset(1, 0).Formula = "=sum(R1C1:R10C1)"
End Sub

It might be interesting to note that passing A1 notation to FormulaR1C1 is
not successful regardless of the users setting while passing R1C1 to Formula
seems to work. However, this was a simple test and I can't say that as a
general rule R1C1 will always work with Formula. Personally if I use
FormulaR1C1, I write the formula string as R1C1 and if I use Formula I write
the formula string as A1.

--
Regards,
Tom Ogilvy



"Ron Rosenfeld" wrote in message
...
On Fri, 13 Oct 2006 21:10:34 -0500, Dave Peterson

wrote:

But if you read help as a directive to you (the programmer) how to write
the
formula in code, then maybe it makes more sense???

Yes, it does. I just never read it that way.
--ron

On Sat, 14 Oct 2006 12:12:35 -0400, "Tom Ogilvy" wrote:

Sub WriteFormula()
ActiveCell.FormulaR1C1 = "=Sum(A1:A10)"
ActiveCell.Offset(1, 0).Formula = "=sum(R1C1:R10C1)"
End Sub

It might be interesting to note that passing A1 notation to FormulaR1C1 is
not successful regardless of the users setting while passing R1C1 to Formula
seems to work. However, this was a simple test and I can't say that as a
general rule R1C1 will always work with Formula. Personally if I use
FormulaR1C1, I write the formula string as R1C1 and if I use Formula I write
the formula string as A1.

It seems as if that is a more prudent way of doing things. It'd be interesting
to know the full documentation for the Formula property, and why R1C1 seems to
work with that, but not the reverse with the FormulaR1C1 property.


--ron

I know that if you mix A1 references style with R1C1 reference style, things
blow up quickly:

ActiveCell.Formula = "=sum(R1C1:R10C1)+B1"

My uninformed guess is that when you enter a formula in excel, excel "sees" it
as R1C1 no matter what your settings are showing. And then it converts the
formula to A1 reference style to show it to the user (if it has to).

I found this interesting.

Try this. Start a new workbook with two sheets.

Change excel to use A1 reference style
Format Cell A1 in Sheet1 as General
Format Cell A1 in Sheet2 as Text
Group these two sheets
With Sheet1 the activesheet
type this in A1
=B1+C1

Now look at the formula entered in A1 of Sheet2.

Kind of interesting, huh?



Ron Rosenfeld wrote:

On Sat, 14 Oct 2006 12:12:35 -0400, "Tom Ogilvy" wrote:

Sub WriteFormula()
ActiveCell.FormulaR1C1 = "=Sum(A1:A10)"
ActiveCell.Offset(1, 0).Formula = "=sum(R1C1:R10C1)"
End Sub

It might be interesting to note that passing A1 notation to FormulaR1C1 is
not successful regardless of the users setting while passing R1C1 to Formula
seems to work. However, this was a simple test and I can't say that as a
general rule R1C1 will always work with Formula. Personally if I use
FormulaR1C1, I write the formula string as R1C1 and if I use Formula I write
the formula string as A1.

It seems as if that is a more prudent way of doing things. It'd be interesting
to know the full documentation for the Formula property, and why R1C1 seems to
work with that, but not the reverse with the FormulaR1C1 property.

--ron

--

Dave Peterson

That was my impression as well - that formulas are "formulated" and/or
"evaluated" in R1C1. This would be consistent with the limitation on
formula length being restricted to the length of the formula measured in the
R1C1 form of expression. Also consistent would be on older version
restrictions that some arguments to some methods in VBA had to be in R1C1.

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
I know that if you mix A1 references style with R1C1 reference style,
things
blow up quickly:

ActiveCell.Formula = "=sum(R1C1:R10C1)+B1"

My uninformed guess is that when you enter a formula in excel, excel
"sees" it
as R1C1 no matter what your settings are showing. And then it converts
the
formula to A1 reference style to show it to the user (if it has to).

I found this interesting.

Try this. Start a new workbook with two sheets.

Change excel to use A1 reference style
Format Cell A1 in Sheet1 as General
Format Cell A1 in Sheet2 as Text
Group these two sheets
With Sheet1 the activesheet
type this in A1
=B1+C1

Now look at the formula entered in A1 of Sheet2.

Kind of interesting, huh?



Ron Rosenfeld wrote:

On Sat, 14 Oct 2006 12:12:35 -0400, "Tom Ogilvy"
wrote:

Sub WriteFormula()
ActiveCell.FormulaR1C1 = "=Sum(A1:A10)"
ActiveCell.Offset(1, 0).Formula = "=sum(R1C1:R10C1)"
End Sub

It might be interesting to note that passing A1 notation to FormulaR1C1
is
not successful regardless of the users setting while passing R1C1 to
Formula
seems to work. However, this was a simple test and I can't say that as a
general rule R1C1 will always work with Formula. Personally if I use
FormulaR1C1, I write the formula string as R1C1 and if I use Formula I
write
the formula string as A1.

It seems as if that is a more prudent way of doing things. It'd be
interesting
to know the full documentation for the Formula property, and why R1C1
seems to
work with that, but not the reverse with the FormulaR1C1 property.

--ron

--

Dave Peterson

On Sat, 14 Oct 2006 12:50:48 -0500, Dave Peterson
wrote:

I know that if you mix A1 references style with R1C1 reference style, things
blow up quickly:

ActiveCell.Formula = "=sum(R1C1:R10C1)+B1"

My uninformed guess is that when you enter a formula in excel, excel "sees" it
as R1C1 no matter what your settings are showing. And then it converts the
formula to A1 reference style to show it to the user (if it has to).

I found this interesting.

Try this. Start a new workbook with two sheets.

Change excel to use A1 reference style
Format Cell A1 in Sheet1 as General
Format Cell A1 in Sheet2 as Text
Group these two sheets
With Sheet1 the activesheet
type this in A1
=B1+C1

Now look at the formula entered in A1 of Sheet2.

Kind of interesting, huh?


Verry interesting.

I suppose the A1 notation style was adopted to differentiate it from Lotus.
--ron