Current Selected Cell

[rpg121]

(This is all needed in the formula bar, not in VBA code)
Is there an argument for a function such as offset to tell the function the current selected cell?

Maybe something along the lines of :
=right(offset(activecell,2,2),5)
Which would takes the last five characters of a cell 2 down and 2 over from the active cell.

I'm trying to make a macro that finds and replaces every cell with a certain string with characters from a cell right beside it plus characters from a cell 2 right from it.

Can anybody help?

 

[waynerenaud]

Try in cell B4 =RIGHT(D6,5) then fill down as required
or look at =cell("address")
or the INDIRECT function
Hope this helps

 

[TonyJollans]

Hi rpg121,

The answer to your actual question ..

Is there an argument for a function [in the formula bar] such as offset to tell the function the current selected cell?

.. is No; that would require the formula to be recalculated every time the selection changed, and I don't think it would provide what you seem to want anyway.

You say

I'm trying to make a macro that finds and replaces every cell with a certain string with characters from a cell right beside it plus characters from a cell 2 right from it.

Finding and replacing things in every cell requires code. A macro is code, yet you say to start with that All this is needed in the formula bar. So what is it you really want?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[rpg121]

I have the code for the finding and replaced every cell with a formula. The problem is the formula you need to supply to use the replace command, it's just a standard formula, and becaus every cell location is going to be different, I need a formula that changes with it. I thought there'd be an "active cell" type argument that would allow me to do that.

All the cells that need to be replaced are scattered around down the A column. Those selected cells need to be replaced by something to make it become the right 5 characters of the one beside it and the whole string of characters from the cell after that (2 over from it).

 

[TonyJollans]

Hi rpg121,

You want to use R1C1 references ..

[blue][tt]Cell.FormulaR1C1 = "=Right(RC[1],5)&RC[2]"[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[combo]

Hi,
What you need is a relative address in formula, you can use RC reference (to a cell you work with) and R[x]C[y] reference (x,y - row and column offset, can be negative).

NB, you can get refernce to the active cell using old macro functions (no, any macro here). Just go Insert>Name, and define, say name Active_Cell as =ACTIVE.CELL(). you can refer in formulas to Active_Cell as to regular range. However, without macros, selecting another cell will do nothing, only forcing recalculation will change reference to new active cell.

combo