Use Offset in formula
 

I'm trying to write a formula in a cell, =Text(B1, "mm/dd") where B1 is the
cell to the right of the cell I'm writing the formula in. Am I at all close
with the following? It's not working. Thanks for your help.

Selection.Formula = "=TEXT(Offset(rowOffset:=0, columnOffset:=1),"mm/dd")"

Use Offset in formula
 

Alex,

If I've understood correctly then try this

ActiveCell.Formula = "=Text(" & ActiveCell.Offset(, 1).Address & ",
""mm/dd"")"

Mike

"Alex" wrote:

I'm trying to write a formula in a cell, =Text(B1, "mm/dd") where B1 is the
cell to the right of the cell I'm writing the formula in. Am I at all close
with the following? It's not working. Thanks for your help.

Selection.Formula = "=TEXT(Offset(rowOffset:=0, columnOffset:=1),"mm/dd")"

Use Offset in formula
 

Try either

=TEXT(OFFSET(B1,0,1),"mm/dd")

or

=TEXT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,1 ),"mm/dd")

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Mon, 15 Dec 2008 10:44:00 -0800, Alex
wrote:

I'm trying to write a formula in a cell, =Text(B1, "mm/dd") where B1 is the
cell to the right of the cell I'm writing the formula in. Am I at all close
with the following? It's not working. Thanks for your help.

Selection.Formula = "=TEXT(Offset(rowOffset:=0, columnOffset:=1),"mm/dd")"

Use Offset in formula
 

This is perfect except that I need the address to be relative.

"Mike H" wrote:

Alex,

If I've understood correctly then try this

ActiveCell.Formula = "=Text(" & ActiveCell.Offset(, 1).Address & ",
""mm/dd"")"

Mike

"Alex" wrote:

I'm trying to write a formula in a cell, =Text(B1, "mm/dd") where B1 is the
cell to the right of the cell I'm writing the formula in. Am I at all close
with the following? It's not working. Thanks for your help.

Selection.Formula = "=TEXT(Offset(rowOffset:=0, columnOffset:=1),"mm/dd")"

Use Offset in formula
 

Then use the optional arguments available to the Address property...

ActiveCell.Formula = "=Text(" & ActiveCell.Offset(, 1).Address(0, 0) & ",
""mm/dd"")"

--
Rick (MVP - Excel)


"Alex" wrote in message
...
This is perfect except that I need the address to be relative.

"Mike H" wrote:

Alex,

If I've understood correctly then try this

ActiveCell.Formula = "=Text(" & ActiveCell.Offset(, 1).Address & ",
""mm/dd"")"

Mike

"Alex" wrote:

I'm trying to write a formula in a cell, =Text(B1, "mm/dd") where B1 is
the
cell to the right of the cell I'm writing the formula in. Am I at all
close
with the following? It's not working. Thanks for your help.

Selection.Formula = "=TEXT(Offset(rowOffset:=0,
columnOffset:=1),"mm/dd")"

Use Offset in formula
 

Hi,

ActiveCell.Formula = "=Text(" & ActiveCell.Offset(, 1).Address(0, 0) & ",
""mm/dd"")"

note the addition of (0,0 after the address

Mike

"Alex" wrote:

This is perfect except that I need the address to be relative.

"Mike H" wrote:

Alex,

If I've understood correctly then try this

ActiveCell.Formula = "=Text(" & ActiveCell.Offset(, 1).Address & ",
""mm/dd"")"

Mike

"Alex" wrote:

I'm trying to write a formula in a cell, =Text(B1, "mm/dd") where B1 is the
cell to the right of the cell I'm writing the formula in. Am I at all close
with the following? It's not working. Thanks for your help.

Selection.Formula = "=TEXT(Offset(rowOffset:=0, columnOffset:=1),"mm/dd")"

Use Offset in formula
 

Sometimes, using the R1C1 reference style makes life easier:

selection.formulaR1C1 = "=text(rc[1],""mm/dd"")"

And remember that if your string includes double quotes ("), you have to double
them ("").





Alex wrote:

I'm trying to write a formula in a cell, =Text(B1, "mm/dd") where B1 is the
cell to the right of the cell I'm writing the formula in. Am I at all close
with the following? It's not working. Thanks for your help.

Selection.Formula = "=TEXT(Offset(rowOffset:=0, columnOffset:=1),"mm/dd")"

--

Dave Peterson

Use Offset in formula
 

Great - thank you.

"Mike H" wrote:

Hi,

ActiveCell.Formula = "=Text(" & ActiveCell.Offset(, 1).Address(0, 0) & ",
""mm/dd"")"

note the addition of (0,0 after the address

Mike

"Alex" wrote:

This is perfect except that I need the address to be relative.

"Mike H" wrote:

Alex,

If I've understood correctly then try this

ActiveCell.Formula = "=Text(" & ActiveCell.Offset(, 1).Address & ",
""mm/dd"")"

Mike

"Alex" wrote:

I'm trying to write a formula in a cell, =Text(B1, "mm/dd") where B1 is the
cell to the right of the cell I'm writing the formula in. Am I at all close
with the following? It's not working. Thanks for your help.

Selection.Formula = "=TEXT(Offset(rowOffset:=0, columnOffset:=1),"mm/dd")"

Use Offset in formula
 

Glad I could help

"Alex" wrote:

Great - thank you.

"Mike H" wrote:

Hi,

ActiveCell.Formula = "=Text(" & ActiveCell.Offset(, 1).Address(0, 0) & ",
""mm/dd"")"

note the addition of (0,0 after the address

Mike

"Alex" wrote:

This is perfect except that I need the address to be relative.

"Mike H" wrote:

Alex,

If I've understood correctly then try this

ActiveCell.Formula = "=Text(" & ActiveCell.Offset(, 1).Address & ",
""mm/dd"")"

Mike

"Alex" wrote:

I'm trying to write a formula in a cell, =Text(B1, "mm/dd") where B1 is the
cell to the right of the cell I'm writing the formula in. Am I at all close
with the following? It's not working. Thanks for your help.

Selection.Formula = "=TEXT(Offset(rowOffset:=0, columnOffset:=1),"mm/dd")"