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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.