String Into a Formula to be Pasted in a Range

[Red Salerno]

I been struggling with this one for awhile, so I thought I would reach out. I'm trying to drop a string into a formula that I want to paste over a named range ("CalcColumn1")

Sub ChangeSectorFormulaCalcs()
Dim Name1 As String
Dim Name2 As String
Dim Name3 As String
Dim Name4 As String
Worksheets("Calculations").Select

Name1 = Range("B7")
Name2 = Range("C7")
Name3 = Range("D7")
Name4 = Range("E7")

Range("CalcColumn1").Formula = "=INDEX(string(Name1)&Data,MATCH($A11,string(Name1)&Dates,0),MATCH(B$9,string(Name1)&Bonds,0))"
End Sub

Any suggestions would be appreciated.

Red

[mrojas]

Does the index function works if you enter it a cell as a formula with literal values?

[Red Salerno]

Yes the index works with literal values.....it is a matrix lookup formula. I want to change part of the string to have the formula look at different matrices based Name1, Name2 etc...

[Red Salerno]

So I tried

Sub ChangeSectorFormulaCalcs()
Dim CalcColumnName1 As String
Dim CalcColumnName2 As String
Dim CalcColumnName3 As String
Dim CalcColumnName4 As String

Dim CalStrformula1 As Variant

Worksheets("Calculations").Select

CalcColumnName1 = Range("B7")
CalcColumnName2 = Range("C7")
CalcColumnName3 = Range("D7")
CalcColumnName4 = Range("E7")\

CalStrformula1 = Index(CalcColumnName1&"Data",MATCH($A11,CalcColumnName1&"CurveDates",0),MAT CH(B$9,CalcColumnName1&"Bonds",0))

End Sub

[EirikDaude]

Is it the actual values of the cells B7:E7 you want to paste into the formula or the addresses? If it is the latter, you need to append .address to the ranges when you define them.

It also look like you are missing a few quotation marks when you are defining your formula, it is a bit hard to read though.

Assuming your first formula is in the correct format, I'd do something like:

Option Explicit

Sub ChangeSectorFormulaCalcs()
  Dim Name1 As String
  Dim Name2 As String
  Dim Name3 As String
  Dim Name4 As String
  
  Dim CalStrformula1 As String
  
  Worksheets("Calculations").Select
  
  Name1 = Range("B7")
  Name2 = Range("C7")
  Name3 = Range("D7")
  Name4 = Range("E7")

  CalStrformula1 = "=INDEX(" + Name1 + "&Data,MATCH($A11," + Name1 + "&Dates,0),MATCH(B$9," + Name1 + "&Bonds,0))"

End Sub

[Red Salerno]

Solved THANKS