Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA returning a cell address
I am trying to write VBA code to return the cell address on one
worksheet within a workbook to another worksheet. The result will be in the simple formula, for example "='Sheet1'!B3" and this formula will be on Sheet 2. I have a macro that is giving the cell on Sheet1 a range name, "FirstCell" in this case, because the cell I want is a moving target. I have another piece of macro code which is the following, Range("D5").Formula = "='Sheet1!" & Range("FirstCell").Address. This is doing a great job of putting the formula ='Sheet1'!$B$3 on Sheet 2 in cell D5. The problem is that it is an absolute reference and I want a relative reference. How can I change this result so I will have the formula ='Sheet1'!B3. Thanks, Ken |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA returning a cell address
Take a look at Address in VBA Help:
One way: Range("D5").Formula = "=Sheet1!" & _ Range("FirstCell").Address(False, False) In article , wrote: I am trying to write VBA code to return the cell address on one worksheet within a workbook to another worksheet. The result will be in the simple formula, for example "='Sheet1'!B3" and this formula will be on Sheet 2. I have a macro that is giving the cell on Sheet1 a range name, "FirstCell" in this case, because the cell I want is a moving target. I have another piece of macro code which is the following, Range("D5").Formula = "='Sheet1!" & Range("FirstCell").Address. This is doing a great job of putting the formula ='Sheet1'!$B$3 on Sheet 2 in cell D5. The problem is that it is an absolute reference and I want a relative reference. How can I change this result so I will have the formula ='Sheet1'!B3. Thanks, Ken |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA returning a cell address
Hi Ken:
Range("FirstCell").Address(False,False) Regards, Vasant. wrote in message om... I am trying to write VBA code to return the cell address on one worksheet within a workbook to another worksheet. The result will be in the simple formula, for example "='Sheet1'!B3" and this formula will be on Sheet 2. I have a macro that is giving the cell on Sheet1 a range name, "FirstCell" in this case, because the cell I want is a moving target. I have another piece of macro code which is the following, Range("D5").Formula = "='Sheet1!" & Range("FirstCell").Address. This is doing a great job of putting the formula ='Sheet1'!$B$3 on Sheet 2 in cell D5. The problem is that it is an absolute reference and I want a relative reference. How can I change this result so I will have the formula ='Sheet1'!B3. Thanks, Ken |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA returning a cell address
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Struggling with returning a cell address | Excel Discussion (Misc queries) | |||
Returning a cell address in a msgbox, by selection via inputbox Ty | Excel Discussion (Misc queries) | |||
Returning an address from an array | Excel Discussion (Misc queries) | |||
returning the address of a value | Excel Discussion (Misc queries) | |||
=(INDIRECT(ADDRESS(ROW(),#))) with SUMPRODUCT returning #VALUE! | Excel Worksheet Functions |