Pulling VBA formulas from worksheet for calculation

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:
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
So I spent a portion of time today on this and am stumped

I have to assume that I've taken the totally wrong path here.

Extra info:
As the macro suggests, n & m are the upper and lower bounds upon which a is calculated.
I've oversimplified this, but verified that it exhibits the same problem as my original attempt.
Is it because V(k) is dimmed as a variant? I've tried others, but nothing worked.

So, how would my macro pick up the formula entered into a cell as a+2, apply it to the value for a at a given time and give the result?

Any help or hints whatsoever would be direly appreciated at this point. I'm getting desparate.
 
Upvote 0
I figured it out on my own!

I was surprised at no response and am curious what I could have done differently to pique enough interest for some help. This board has saved me in the past, but this time I wasn't so lucky.

It turns out that if I modified the code to use the following to calculate the value of the user-entered formula I was OK. I replaced

Code:
Cells(Row, k).Formula = v(k)

with
Code:
Cells(Row, k).Formula = Evaluate(Application.WorksheetFunction.Substitute(d(k), "a", a))

Worked like a charm, but I wonder if there would have been a way to do this without using a worksheet function.

I guess I'll research that one on my own as well.

Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top