How can I:

- refer to the value of a cell in column B, in the same row as the active
cell?
- Assign a cell reference to a variable, so that I can later assign it a
value, as in MyCellRef = someaddress; MyCellRef.Formula = @sum(BegVal..EndVal)

Thanks for any and all assistance.
Sprinks

Sprinks,

Try

Dim Rng As Range
Set Rng = ActiveCell.EntireRow.Cells(1,"B")
Rng.Formula = "=SUM(A1:A10)"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Sprinks" wrote in message
...
How can I:

- refer to the value of a cell in column B, in the same row as
the active
cell?
- Assign a cell reference to a variable, so that I can later
assign it a
value, as in MyCellRef = someaddress; MyCellRef.Formula =
@sum(BegVal..EndVal)

Thanks for any and all assistance.
Sprinks

if you want to learn to program VBA,
please do yourself a favour:
buy (and read) a decent book like WalkenBach's
Excel 2003 Power Programming


From your little snippet I presume your an (ex) lotus user.
in excel formulas always start with = not @

'define a variable
dim rng as range

'Assign it to a "known" address
set rng = ActiveSheet.Range("A1")

'Assign it to the cell in column B on the "current" cell's row.

Set rng = Activecell.EntireRow.Cells(1,2)


For formulas with relative referencing it's easiest to use the
FormulaR1C1 property...
sum column2 to column4 on the current row..

rng.FormulaR1C1 = "=SUM(RC2:RC4)"


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"?B?U3ByaW5rcw==?=" wrote:

How can I:

- refer to the value of a cell in column B, in the same row as the
active cell?
- Assign a cell reference to a variable, so that I can later assign it
a value, as in MyCellRef = someaddress; MyCellRef.Formula =
@sum(BegVal..EndVal)

Thanks for any and all assistance.
Sprinks

Hi,

1. refer to the value of a cell in column B, in the same row as the active
cell
cells(activecell.Row,2)

2.Assign a cell reference to a variable
Dim MyCellRef as String
MyCellRef =Activecell.address
Range(MyCellRef ).formula = )"
Or
Dim MyCell as Range
Set MyCell =Activecell 'or range("A1") for example

MyCell.formula = )"

'when finished
set MyCell = Nothing

Regards,

Jean-Yves



"Sprinks" wrote in message
...
How can I:

- refer to the value of a cell in column B, in the same row as the active
cell?
- Assign a cell reference to a variable, so that I can later assign it a
value, as in MyCellRef = someaddress; MyCellRef.Formula =
@sum(BegVal..EndVal)

Thanks for any and all assistance.
Sprinks

Thanks, Chip. Just what I was looking for.

Sprinks

"Chip Pearson" wrote:

Sprinks,

Try

Dim Rng As Range
Set Rng = ActiveCell.EntireRow.Cells(1,"B")
Rng.Formula = "=SUM(A1:A10)"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Sprinks" wrote in message
...
How can I:

- refer to the value of a cell in column B, in the same row as
the active
cell?
- Assign a cell reference to a variable, so that I can later
assign it a
value, as in MyCellRef = someaddress; MyCellRef.Formula =
@sum(BegVal..EndVal)

Thanks for any and all assistance.
Sprinks

keepItcool,

Thank you for your solutions and book recommendation.

'@' -- guess you identified my age, too. Anyone in business my age is
an ex-Lotus-user!

Sprinks

"keepITcool" wrote:

if you want to learn to program VBA,
please do yourself a favour:
buy (and read) a decent book like WalkenBach's
Excel 2003 Power Programming


From your little snippet I presume your an (ex) lotus user.
in excel formulas always start with = not @

'define a variable
dim rng as range

'Assign it to a "known" address
set rng = ActiveSheet.Range("A1")

'Assign it to the cell in column B on the "current" cell's row.

Set rng = Activecell.EntireRow.Cells(1,2)


For formulas with relative referencing it's easiest to use the
FormulaR1C1 property...
sum column2 to column4 on the current row..

rng.FormulaR1C1 = "=SUM(RC2:RC4)"


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"?B?U3ByaW5rcw==?=" wrote:

How can I:

- refer to the value of a cell in column B, in the same row as the
active cell?
- Assign a cell reference to a variable, so that I can later assign it
a value, as in MyCellRef = someaddress; MyCellRef.Formula =
@sum(BegVal..EndVal)

Thanks for any and all assistance.
Sprinks

Thanks, Jean-Yves. More "arrows in the quiver".

Best regards.

Sprinks

"Jean-Yves" wrote:

Hi,

1. refer to the value of a cell in column B, in the same row as the active
cell
cells(activecell.Row,2)

2.Assign a cell reference to a variable
Dim MyCellRef as String
MyCellRef =Activecell.address
Range(MyCellRef ).formula = )"
Or
Dim MyCell as Range
Set MyCell =Activecell 'or range("A1") for example

MyCell.formula = )"

'when finished
set MyCell = Nothing

Regards,

Jean-Yves



"Sprinks" wrote in message
...
How can I:

- refer to the value of a cell in column B, in the same row as the active
cell?
- Assign a cell reference to a variable, so that I can later assign it a
value, as in MyCellRef = someaddress; MyCellRef.Formula =
@sum(BegVal..EndVal)

Thanks for any and all assistance.
Sprinks