Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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")" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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")" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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")" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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")" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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")" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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")" |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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")" |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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")" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OFFSET Formula | Excel Discussion (Misc queries) | |||
1 OFFSET FORMULA, I'M NOT SURE WHAT DOES IT MEAN!!!!!! | Excel Discussion (Misc queries) | |||
Offset formula | Excel Discussion (Misc queries) | |||
Offset in Formula | Excel Discussion (Misc queries) | |||
Offset Formula | Excel Programming |