• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combining variable cell addresses into CONCATENATE using VBA

W

Wulluby

Member
Hi,

It must have been a while since I last logged in, the new look looks good.

Been banging my head on this one for a while, seems like it should be easy but I am missing something to make it work.

I want the following in an address, the problem is that the cell references can vary each time the macro is run:
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""This is "",R[-1]C[7],"" and that is "",R[-3]C[7])"

When the macro runs I use Dim rngThis As Range, same with rngThat in order to catch the new cell reference. If I enter rngThis into a cell with ActiveCell.FormulaR1C1 = rngThis.Address then I can see the address.

The problem I am having is combining it into:
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""This is "",rngThis.address,"" and that is "",rngThat.address)"

What is entered is literally rngThis.address as opposed to the cell address.

Thanks in advance.
 
The variable is not getting evaluated. Maybe this:
Code: 
ActiveCell.Formula = _
"=CONCATENATE(""This is ""," & rngThis.Address & ","" and that is ""," & rngThat.Address & ")"
 
You must log in or register to reply here.
Back
Top