R1C1 Fix

[Poseur]

I'm sure somebody knows a better way, but...
I have this sheet I take from messed up data and make it
useable. The problem is that the # of rows and columns varies
from one instance to the next.
So, I did the formulas by hand (fx wizard, etc) and then
switched on R1C1 and lo and behold the reference could be more
easily made dynamic by putting together a string with the
correct row and column variables, like this:

MyRange.FormulaR1C1 = "=SUM(RC(2 - lastcolumn):RC[-1]"

But, of course, the application doesn't know what the variable
"lastcolumn" is so it gets pasted literal and a #NAME# thing
comes up. But this works:

MyRange.FormulaR1C1 = "=SUM(" & RCFix(, 2 - lastcolumn) & ":"
& RCFix(, -1) & ")"

with the helper function:
Function RCFix(Optional r As Integer, Optional c As Integer)
As String
Dim rstrng As String, cstrng As String
If r = 0 Then
rstrng = "R"
ElseIf r < 0 Then
rstrng = "R[" & Format(r) & "]"
Else
rstrng = "R" & Format(r)
End If
If c = 0 Then
cstrng = "C"
ElseIf c < 0 Then
cstrng = "C[" & Format(c) & "]"
Else
cstrng = "C" & Format(c)
End If
RCFix = rstrng & cstrng
End Function

which produces, in this case:
"=SUM(RC2:RC[-1])"
or

MyRange.FormulaR1C1 = "=SUM(RC2:RC[-1])"

Which works good. I don't know if that is useful to anyone but
me but whenever I spend a few hours figuring something out I
put it here sort of as a reward.
I'm interested to see some better ways too.

 

[Bob Phillips]

You don't need R1C1 style, which I always find non-intuitive in the
spreadsheet, as you can plug row numbers into an A1 style formula, suvh as

myRange.Formula = "=SUM(A1:A" & lastorw & ")"

if you want columns as well you can use

myRange.Formula = "=SUM(A1:" & ColumnLetter(lastcolumn) & lastrow & ")"


'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
sColumn = Split(Columns(Col).Address(, False), ":")(1)
ColumnLetter = sColumn
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tushar Mehta]

Hope you don't mind some constructive criticism. ;-)

While I recognize the superiority of the R1C1 addressing scheme,
decades of indoctrination with A1 makes it harder to switch to it --
and also the fact that if I spent the time to un-brainwash myself, it
would become that much harder to communicate with others who are still
stuck with A1 -- the old qwerty or betamax dilemmas. Where it works
much better is when I am developing code. It is a much more logical
way of thinking.

That said...

First, something specific to your code. RCFix is incorrect. You need
the square brackets when using a relative reference -- any relative
reference. Here are the rules:
A number without a square bracket pair is an absolute reference.
A number within square brackets implies a relative reference.
Add the number to the current cell's row (or column) to get the
row (or column) of the referenced cell. So, a positive number is
to the right (for column) or below (for row). And, of course, a
negative number goes in the other direction.
The absence of a number (and brackets) is a reference to 'this' --
row or column as the case may be. It is the equivalent of [0].

So, =RC[1] would be a reference to a cell one to the right in the same
row as the cell containing the formula, i.e., 'this row one column to
the right.' =RC1, on the other hand, would be 'this row column 1,' and
=R1C1 would be a reference to row 1 column 1.

Second and last point, XL supports an incredibly large number of very
powerful properties and methods for the Range object -- something often
overlooked even by experienced developers. You don't explain what
lastcolumn in your code represents and I am somewhat confused by the
use of 2-lastcolumn.

Suppose you want to create the formula =SUM(this-row-column-2:this-row-
this-column-minus-1). Depending on what your *intent* is, consider:
myCell.Formula = "=SUM(" & Range(Cells(myCell.Row, 2), _
myCell.Offset(0, -1)).Address & ")"
or
myCell.Formula = "=SUM(" & Range(Cells(myCell.Row, 2), _
myCell.Offset(0, -1)).Address(False, True) & ")"

or
myCell.Formula = "=SUM(" _
& Cells(myCell.Row, 2).Address(False, True) _
& "," & myCell.Offset(0, -1).Address(False, False) & ")"

The first creates a formula with absolute references, i.e., =SUM
(R14C2:R14C5). The second leaves the row relative and uses absolute
columns, i.e., =SUM(RC2:RC5), while the last duplicates your code,
i.e., =SUM(RC2,RC[-1])

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I'm sure somebody knows a better way, but...
I have this sheet I take from messed up data and make it
useable. The problem is that the # of rows and columns varies
from one instance to the next.
So, I did the formulas by hand (fx wizard, etc) and then
switched on R1C1 and lo and behold the reference could be more
easily made dynamic by putting together a string with the
correct row and column variables, like this:

MyRange.FormulaR1C1 = "=SUM(RC(2 - lastcolumn):RC[-1]"

But, of course, the application doesn't know what the variable
"lastcolumn" is so it gets pasted literal and a #NAME# thing
comes up. But this works:

MyRange.FormulaR1C1 = "=SUM(" & RCFix(, 2 - lastcolumn) & ":"
& RCFix(, -1) & ")"

with the helper function:
Function RCFix(Optional r As Integer, Optional c As Integer)
As String
Dim rstrng As String, cstrng As String
If r = 0 Then
rstrng = "R"
ElseIf r < 0 Then
rstrng = "R[" & Format(r) & "]"
Else
rstrng = "R" & Format(r)
End If
If c = 0 Then
cstrng = "C"
ElseIf c < 0 Then
cstrng = "C[" & Format(c) & "]"
Else
cstrng = "C" & Format(c)
End If
RCFix = rstrng & cstrng
End Function

which produces, in this case:
"=SUM(RC2:RC[-1])"
or

MyRange.FormulaR1C1 = "=SUM(RC2:RC[-1])"

Which works good. I don't know if that is useful to anyone but
me but whenever I spend a few hours figuring something out I
put it here sort of as a reward.
I'm interested to see some better ways too.

 

[Poseur]

Hope you don't mind some constructive criticism. ;-)

Not at all!

While I recognize the superiority of the R1C1 addressing
scheme, decades of indoctrination with A1 makes it harder
to switch to it...Where it works much better is when I am

developing code.

I think I got seduced into it when I noticed that the relative
reference formula is the same from row to row and column to
column so I could copy it down the rows or across the columns
without having to increment it.

First, something specific to your code. RCFix is
incorrect.

Yeah, I found that out later last night. It's interesting how
little is written about R1C1.There is 1 book by Bill "Mr.
Excel" Jelen that covers it very well but it was buried under
some stuff last night.

You don't explain what lastcolumn
in your code represents and I am somewhat confused by the
use of 2-lastcolumn.

Each instance of the sheet (a data list, really) populates
rows and columns with a variable # of fields depending on how
many people did how many types of services. So, I can't really
use a template without a lot of cutting and pasting. The "2-
lastcolumn" comes from this (hope formatting holds):
service service service total
doer # # # formula
doer # # # formula
doer # # # formula

So formula = SUM(this row, from lastcolumn - 2 to lastcolumn -
1.

Thanks for your interest (and the previous poster re codifying
A1). I will work through your examples.

 

[Poseur]

Hope you don't mind some constructive criticism. ;-)

T,
Thanks!. Where was that "address" property when I was looking
for an answer to my dilemma? Can I call you at 3 am next time
I'm hacking wildly?

 

[Tushar Mehta]

You are welcome.

And about calling me at 3 a.m., I guess it is OK. Just as long as you
are OK with "What the F**K!" Bang. Dial-tone.

You might be better off digging for the one book that explains whatever
you are stuck with. {grin} Though, personally, I would jump on to
msdn.microsoft.com or the web sites of some of the MVPs. That will get
you unstuck in about 99% of the cases.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[mitchel]

http://www.ardice.com/Arts/Movies/Titles/3/3_AM