Proper Syntax Sought For NewBie Excel Programmer

[Guest]

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

 

[Chip Pearson]

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

 

[keepITcool]

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 >

 

[Jean-Yves]

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 = "=@sum(BegVal..EndVal)"
Or
Dim MyCell as Range
Set MyCell =Activecell 'or range("A1") for example

MyCell.formula = "=@sum(BegVal..EndVal)"

'when finished
set MyCell = Nothing

Regards,

Jean-Yves

 

[Guest]

Thanks, Chip. Just what I was looking for.

Sprinks

 

[Guest]

keepItcool,

Thank you for your solutions and book recommendation.

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

Sprinks

 

[Guest]

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

Best regards.

Sprinks