Use Offset in formula

[Alex]

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

 

[Mike H]

Alex,

If I've understood correctly then try this

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

Mike

 

[Chip Pearson]

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)

 

[Alex]

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

 

[Rick Rothstein]

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

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

 

[Mike H]

Hi,

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

note the addition of (0,0 after the address

Mike

 

[Dave Peterson]

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]

Great - thank you.

Hi,

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

note the addition of (0,0 after the address

Mike

 

[Mike H]

Glad I could help

Great - thank you.