Concatenating a cell address with a formula....

[mattst]

For anyone who's willing to help out a beginner...

I'm trying to insert a formula in a cell as part of a macro but I can't seem to get it to work. This is the code I'm using to do that.

cell4.FormulaR1C1 = "=RC[-2]/" & cell3.Address

Which gives me an error.
However, if I do this, I get no error, but it obviously doesn't get the job done:

cell4.FormulaR1C1 = "RC[-2]/" & cell3.Address

Why does the "=" cause an error when concatenating. (It doesn't cause an error when I leave out the "& cell3.Address".) And is there any easy way to get around this?

 

[SkipVought]

Hi,

Do you REALLY want to use R1C1 notation in your formula?

why not...

   set cell4 = activecell
   set cell3 = cells(5, "B")
   with cell4
     if cell3.value <> 0 then
        .formula = "=" & .offset(0,-2).address & "/" & cell3.address
     end if
   end with

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[TonyJollans]

Hi mattst,

Skip is probably correct but his code doesn't quite do what yours would have done - although it's a minor adjustment to make it so.

As you say you're a beginner I'll try and give an explanation.

The reason your version without the "=" sign works is because you are simply inserting the TEXT string "RC[-2]/$B$5" in the cell (I don't know your actual cell address so $B$5 is by way of example).

Next, the reason your version with the "=" doesn't work is because you are mixing up two different reference styles. "RC[-2]" is in R1C1 reference style and is relative, meaning "this row, two columns to the left". "cell3.address" is, by default, in A1 reference style and absolute, meaning the cell in the fifth row, second column of the worksheet (sticking with the same example of B5) no matter what cell the formula is in.

To make your code work you must use a consistent reference style. If you want to make cell3.address appear in R1C1 style you must add a parameter (the third one - called ReferenceStyle) to the address method .. [blue]cell3.address(,,xlR1C1)[/blue]. This will work but it will produce a formula which includes one relative address and one absolute address (which may be what you want) such as [tt]=RC[-2]/R5C2[/tt] (still using B5 as example).

An alternative way to code is to do what Skip shows. In this case he is using A1 reference style but it doesn't actually matter - what matters is that both addresses are presented in the same way - here simply as "(cell).address".

Finally, where Skip's code differs from yours is that his reference to cell4.offset(0,-2) is absolute. To reproduce what you were trying to do it must be changed to be relative. This is done, again, by passing parameters to the address method - this time the first and second, called RowAbsolute and ColumnAbsolute respectively - .. [blue]cell4.offset(0,-2).address(False,False)[/blue].

I hope that gives you enough information to mix and match all the elements you want to produce the formula you want using any sort of references.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[