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...
Code
Option Explicit
Sub formulaset()
Dim Cell As Range
Dim target As String
For Each Cell In Range("b4:al132")
Application.ScreenUpdating = False
target = Cell.Address
Cell.FormulaR1C1 = "=INDIRECT(CONCATENATE(BackEnd!E15,""!"",target))"
Application.ScreenUpdating = True
Next Cell
End Sub
Display More
but this is the answer I am getting in the first cell of the range...
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.