PDA

View Full Version : [SOLVED] String Into a Formula to be Pasted in a Range



Red Salerno
11-21-2013, 03:39 PM
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
11-21-2013, 04:42 PM
Does the index function works if you enter it a cell as a formula with literal values?

Red Salerno
11-21-2013, 06:54 PM
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
11-21-2013, 08:41 PM
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),MATCH(B$9,CalcColumnName1&"Bonds",0))

End Sub

EirikDaude
11-22-2013, 12:57 AM
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
11-22-2013, 08:35 AM
Solved THANKS