ctrlaltdel
Board Regular
- Joined
- Dec 1, 2002
- Messages
- 60
Hello wonderful gurus! It's been a long time since I posted here.
I am bumbling about trying to do something that I am not even sure I can explain. Here's my attempt:
I would like an end-user to be able to enter the text for 6 formulas using predefined variables into 6 different cells in a worksheet. These formulas are as simple as a/2, a-2, a-3, a+1, a*2, a*4, but can vary depending on the user and the need. The use also defines the range of values that the variable a can encompass.
I am trying to write a macro that will fill a series of cells with the result of the formula for each value of the variable a. My problem is that I can't figure out the proper context for pulling the formula's from the excel, having them calculate based on the current value of a and then return the result rather than the formula itself.
Here is what I have, but it is flawed:
This just keeps giving me the formulas rather than their values. Anyone know how I might make this work?
Thank you,
James
I am bumbling about trying to do something that I am not even sure I can explain. Here's my attempt:
I would like an end-user to be able to enter the text for 6 formulas using predefined variables into 6 different cells in a worksheet. These formulas are as simple as a/2, a-2, a-3, a+1, a*2, a*4, but can vary depending on the user and the need. The use also defines the range of values that the variable a can encompass.
I am trying to write a macro that will fill a series of cells with the result of the formula for each value of the variable a. My problem is that I can't figure out the proper context for pulling the formula's from the excel, having them calculate based on the current value of a and then return the result rather than the formula itself.
Here is what I have, but it is flawed:
Code:
Sub formulahelp()
Dim a As Integer ' the value I'm looking for
Dim n As Integer 'lower bound for a
Dim m As Integer 'upper bound for a
Dim v(6) As Variant 'formulas from spreadsheet
Dim i As Integer 'Counter
Dim Row As Integer 'to give correct row
'get bounds from sheet
n = Cells(1, 2)
m = Cells(2, 2)
For i = 1 To 6
v(i) = Cells(i + 2, 2)
Next i
'Fill the sheet in
Row = 10
For i = n To m
For k = 1 To 6
a = i
Cells(Row, k).Formula = v(k)
Next k
Row = Row + 1
Next i
End Sub
This just keeps giving me the formulas rather than their values. Anyone know how I might make this work?
Thank you,
James