How to specify Non-absolute formulae references in code

G

Guest

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
 
C

Chip Pearson

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)
 
G

Guest

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

regards, Alan

*From:* Chip Pearson <[email protected]>
*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)





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
Click to expand...
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
Click to expand...
uses a >list on the sheet created, the formula SUM's on the
(shorter) list.
Is this possible?
Click to expand...
Click to expand...
 
G

Guest

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?

*From:* Chip Pearson <[email protected]>
*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)


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
Click to expand...
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
Click to expand...
Click to expand...


regards, Alan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calling Specific sheet in workbook with Code 2

Top