Add Indirect Formula Via Macro

  • I need to create a formula for a series of ranges that have a variable sheet name (which is located on sheet Backend!E15) and when it creates the formula will reference the exact same cell on the variable sheet.


    this is what i have so far...



    but this is the answer I am getting in the first cell of the range...


    Code
    =INDIRECT(CONCATENATE(BackEnd!'E15',"!",target))


    as you can see I am having trouble getting the target address to lock in. To make things worse, its needs to be in " " so the concatenate creates the corect address link.


    Any help is much appreciated.

  • Re: Indirect Vba Formula


    here is the other problem. Where the "Target" is entered in the formula...it has to be in " ". So that it is observed as text for the Concatenate to work. The indirect function activates after reading the completed concatenate.


    I have been looking at this for so long its killing me.

  • Re: Add Indirect Formula Via Macro


    thanks to all for the help. I finally got it...


    Here is the solution.


    Code
    target = Cell.Address
        Cell.Formula = "=INDIRECT(CONCATENATE(" & Range("BackEnd!E15") _
            .Address(, , , True) & ",""!"",""" & target & """))"


    Again, I needed the cell address to be entered as " ".


    Dave, By recording it sets an actual reference to the cell address...so it ends up being a circular reference since the concatenate is trying to use the address as opposed to joining the "name" of an address. Thanks anyway...the recorder is actually what I used to get me started.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!