Hello and thanks for any assistance proffered.

(I am on a steep part of a learning curve with VBA language/syntax)

I have the following (working) code as part of a procedure. It is an
addition to a hacked about hybrid version of Ron de Bruin's
Copy_To_Worksheets procedure.

The sub is fake for the sake of a working demo.

Sub formulaPut()

' Earlier definitions
Dim AnalStart As Long, AnalEnd As Long, cSheet As String
Dim SumLastRow As Long, FormulaRow As Long, nCol As Long

' Set in a definitions sheet
AnalStart = 3 ' "Start Column"
AnalEnd = 12 ' "End Column"
cSheet = "Sheet1" ' (for the sake of demo)

' Starting at row 2
SumLastRow = 9 ' LastRow() calculation
FormulaRow = SumLastRow + 2

' Add formulae and make them bold
Worksheets(cSheet).Activate
For nCol = AnalStart To AnalEnd
Cells(FormulaRow, nCol).FormulaR1C1 = _
"=sum(R2C" & nCol & ":" & "R" & SumLastRow & "C" & nCol & ")"

Cells(FormulaRow, nCol).Font.Bold = True
Cells(FormulaRow, nCol).NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Next nCol

End Sub

This produces something like =SUM($F$2:$F$39), which is perfectly OK.

However, I would prefer =SUM(F2:F39) or $F2:$F39), so if anyone uses a
list on the sheet created, the formula SUM's on the (shorter) list.

Is this possible?

regards, Alan

Instead of using FormulaR1C1, just use Formula. E.g.,

Sub AAA()
Dim StartRow As Long
Dim EndRow As Long
StartRow = 1
EndRow = 10

Range("A1").Formula = "=SUM(B" & CStr(StartRow) & _
":B" & CStr(EndRow) & ")"
End Sub


The reason you get the absolute references (with the $ characters) is
that you assign the formula as an R1C1 reference style and Excel
translates that to A1 style when it puts it in the cell.

If you want to keep the R1C1 style in the VBA code, put the R1C1
formula in a String variable, call ConvertFormula and Replace:

Dim S As String
S = "=SUM(R1C1:R10C1)"
S = Replace( _
Application.ConvertFormula(S, xlR1C1, xlA1, False), _
"$", vbNullString)
Range("B1").Formula = S

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





On Thu, 10 Sep 2009 17:10:08 -0500,

wrote:

Hello and thanks for any assistance proffered.

(I am on a steep part of a learning curve with VBA language/syntax)

I have the following (working) code as part of a procedure. It is an
addition to a hacked about hybrid version of Ron de Bruin's
Copy_To_Worksheets procedure.

The sub is fake for the sake of a working demo.

Sub formulaPut()

' Earlier definitions
Dim AnalStart As Long, AnalEnd As Long, cSheet As String
Dim SumLastRow As Long, FormulaRow As Long, nCol As Long

' Set in a definitions sheet
AnalStart = 3 ' "Start Column"
AnalEnd = 12 ' "End Column"
cSheet = "Sheet1" ' (for the sake of demo)

' Starting at row 2
SumLastRow = 9 ' LastRow() calculation
FormulaRow = SumLastRow + 2

' Add formulae and make them bold
Worksheets(cSheet).Activate
For nCol = AnalStart To AnalEnd
Cells(FormulaRow, nCol).FormulaR1C1 = _
"=sum(R2C" & nCol & ":" & "R" & SumLastRow & "C" & nCol & ")"

Cells(FormulaRow, nCol).Font.Bold = True
Cells(FormulaRow, nCol).NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Next nCol

End Sub

This produces something like =SUM($F$2:$F$39), which is perfectly OK.

However, I would prefer =SUM(F2:F39) or $F2:$F39), so if anyone uses a
list on the sheet created, the formula SUM's on the (shorter) list.

Is this possible?

regards, Alan

Thank you for your (rapid) solution, explanation and for introducing me to
CStr()! I will implement this later.

regards, Alan

In article ,
(Chip Pearson) wrote:

*From:* Chip Pearson
*Date:* Thu, 10 Sep 2009 17:50:16 -0500

Instead of using FormulaR1C1, just use Formula. E.g.,

Sub AAA()
Dim StartRow As Long
Dim EndRow As Long
StartRow = 1
EndRow = 10

Range("A1").Formula = "=SUM(B" & CStr(StartRow) & _
":B" & CStr(EndRow) & ")"
End Sub


The reason you get the absolute references (with the $ characters)
is
that you assign the formula as an R1C1 reference style and Excel
translates that to A1 style when it puts it in the cell.

If you want to keep the R1C1 style in the VBA code, put the R1C1
formula in a String variable, call ConvertFormula and Replace:

Dim S As String
S = "=SUM(R1C1:R10C1)"
S = Replace( _
Application.ConvertFormula(S, xlR1C1, xlA1, False), _
"$", vbNullString)
Range("B1").Formula = S

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





On Thu, 10 Sep 2009 17:10:08 -0500,

wrote:

Hello and thanks for any assistance proffered.

(I am on a steep part of a learning curve with VBA language/syntax)

I have the following (working) code as part of a procedure. It is
an addition to a hacked about hybrid version of Ron de Bruin's
Copy_To_Worksheets procedure.

The sub is fake for the sake of a working demo.

Sub formulaPut()

' Earlier definitions
Dim AnalStart As Long, AnalEnd As Long, cSheet As String
Dim SumLastRow As Long, FormulaRow As Long, nCol As Long

' Set in a definitions sheet
AnalStart = 3 ' "Start Column"
AnalEnd = 12 ' "End Column"
cSheet = "Sheet1" ' (for the sake of demo)

' Starting at row 2
SumLastRow = 9 ' LastRow() calculation
FormulaRow = SumLastRow + 2

' Add formulae and make them bold
Worksheets(cSheet).Activate
For nCol = AnalStart To AnalEnd
Cells(FormulaRow, nCol).FormulaR1C1 = _
"=sum(R2C" & nCol & ":" & "R" & SumLastRow & "C" & nCol
& ")"

Cells(FormulaRow, nCol).Font.Bold = True
Cells(FormulaRow, nCol).NumberFormat =
"#,##0.00_);[Red](#,##0.00)"
Next nCol

End Sub

This produces something like =SUM($F$2:$F$39), which is perfectly
OK.

However, I would prefer =SUM(F2:F39) or $F2:$F39), so if anyone
uses a list on the sheet created, the formula SUM's on the
(shorter) list.

Is this possible?

Thank you for your suggestion, which I eventually got working (both ways).

However, I wanted to put formulae along a row and I couldn't find a
function to do the reverse of Column() - (to be able to create A, B, AA,
AB etc so created one.

The non-absolute formulae were created just fine, so thanks for that.

However the idea behind doing this was to have the SUM() change if the
sheet were changed to a list and only a sub-section providing a shorter
list to add up. This did NOT work.

So my overall logic was wrong. (Nothing new here then!)

Can the formulae be made to add up only the visible cells? I think a pivot
table does this?

Would it be better to create a / some pivot table(s) instead?

In article ,
(Chip Pearson) wrote:

*From:* Chip Pearson
*Date:* Thu, 10 Sep 2009 17:50:16 -0500

Instead of using FormulaR1C1, just use Formula. E.g.,

Sub AAA()
Dim StartRow As Long
Dim EndRow As Long
StartRow = 1
EndRow = 10

Range("A1").Formula = "=SUM(B" & CStr(StartRow) & _
":B" & CStr(EndRow) & ")"
End Sub


The reason you get the absolute references (with the $ characters)
is
that you assign the formula as an R1C1 reference style and Excel
translates that to A1 style when it puts it in the cell.

If you want to keep the R1C1 style in the VBA code, put the R1C1
formula in a String variable, call ConvertFormula and Replace:

Dim S As String
S = "=SUM(R1C1:R10C1)"
S = Replace( _
Application.ConvertFormula(S, xlR1C1, xlA1, False), _
"$", vbNullString)
Range("B1").Formula = S

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 10 Sep 2009 17:10:08 -0500,

wrote:

Hello and thanks for any assistance proffered.

(I am on a steep part of a learning curve with VBA language/syntax)

I have the following (working) code as part of a procedure. It is
an addition to a hacked about hybrid version of Ron de Bruin's
Copy_To_Worksheets procedure.

The sub is fake for the sake of a working demo.

Sub formulaPut()

' Earlier definitions
Dim AnalStart As Long, AnalEnd As Long, cSheet As String
Dim SumLastRow As Long, FormulaRow As Long, nCol As Long

' Set in a definitions sheet
AnalStart = 3 ' "Start Column"
AnalEnd = 12 ' "End Column"
cSheet = "Sheet1" ' (for the sake of demo)

' Starting at row 2
SumLastRow = 9 ' LastRow() calculation
FormulaRow = SumLastRow + 2

' Add formulae and make them bold
Worksheets(cSheet).Activate
For nCol = AnalStart To AnalEnd
Cells(FormulaRow, nCol).FormulaR1C1 = _
"=sum(R2C" & nCol & ":" & "R" & SumLastRow & "C" & nCol
& ")"

Cells(FormulaRow, nCol).Font.Bold = True
Cells(FormulaRow, nCol).NumberFormat =
"#,##0.00_);[Red](#,##0.00)"
Next nCol

End Sub

This produces something like =SUM($F$2:$F$39), which is perfectly
OK.

However, I would prefer =SUM(F2:F39) or $F2:$F39), so if anyone
uses a list on the sheet created, the formula SUM's on the
(shorter) list.

Is this possible?

regards, Alan


regards, Alan