Simultaneous Function integration and Goal Seek over multiple cell

[Swapnil_excel]

Dear Members,
I want your help in writing a macro (or macros) that can calculate definite integral of a function (of single variable) and simultaneously minimize the variable as well. Is it even possible or i have to write to separate macros for that.
E.g. i wish to minimize a function F(x) = ∫f(x)-constant over lower and upper limit of x, where x is unknown (but > 0) and the same has to be done over a number of rows (for which i have written a macro.

Can you help me out I am posting the macro that i have in to parts, i.e. for goal seek (minimization) and integration (found this one on web, by 'By Christos Samaras at, http://www.myengineeringworld.net ). Both of these macros work fine (individually), how ever when i paste them in single module....things go wrong.

Please suggest a remedy....

Sub Macro1()
'
' Macro1 Macro

'
' Keyboard Shortcut: Ctrl+Shift+M

For j = 21 To 120
Cells(j, "O").GoalSeek Goal:=0, ChangingCell:=Cells(j, "K")
Cells(j, "P").GoalSeek Goal:=0, ChangingCell:=Cells(j, "L")
Next j
End Sub



Function SimpsonIntegral(InputFunction As String, Xstart As Double, _
Xend As Double, NumberOfIntervals As Long) As Variant

'----------------------------------------------------------------------------------------------------------
'Calculates the integral of InputFunction using the Composite Simpson's Rule.

'InputFunction is the function to integrate, expressed as a function of "xi".
'Examples: "5*xi + 3", "4*xi^5 + 5*xi^2 + 3*xi + 5", "cos(xi) + tan(xi)".

'Xstart is the initial value of xi.
'Xend is the final value of xi.

'NumberOfIntervals used for integration (rounded up to an even number).
'Bear in mind that this value should be large enough in order to achieve
'a sufficeint degree of accuracy. As a drawback it takes longer computational time to complete.

'Function used directly in a worksheet: =SimpsonIntegral("32*xi^5 + 5*xi^3 + 12*xi + 1"; 0; 100; 1000)
'Note that depending on your settings you might need to change the ";" with ",".
'Function called in VBA: Result = SimpsonIntegral("4*xi^3 + 5*xi^(-1/2) + 5", 1, 10, 100)

'By Christos Samaras
'Date: 23/6/2013
'[URL='http://www.myengineeringworld.net/']http://www.myengineeringworld.net[/URL]
'----------------------------------------------------------------------------------------------------------

'Declaring the necessary variables.
Dim i As Long
Dim TheStep As Double
Dim Cumulative As Double

'Check if the NumberOfIntervals is a valid number.
If NumberOfIntervals < 1 Then
SimpsonIntegral = "The number of intervals must be > 0!"
Exit Function
End If

On Error GoTo ErrorHandler

'Check if the initial and final value of xi are different.
If Xstart = Xend Then
SimpsonIntegral = "Xstart must be different than Xend!"
Exit Function
End If

'Make the NumberOfIntervals even number.
NumberOfIntervals = 2 * NumberOfIntervals

'Calculating the step value.
TheStep = (Xend - Xstart) / NumberOfIntervals

'Calculating the initial value for Xstart.
Cumulative = FunctionResult(InputFunction, Xstart)

'Loop for odd values.
For i = 1 To NumberOfIntervals - 1 Step 2
Cumulative = Cumulative + 4 * FunctionResult(InputFunction, Xstart + i * TheStep)
Next i

'Loop for even values.
For i = 2 To NumberOfIntervals - 2 Step 2
Cumulative = Cumulative + 2 * FunctionResult(InputFunction, Xstart + i * TheStep)
Next i

'Calculating the final value for Xend.
Cumulative = Cumulative + FunctionResult(InputFunction, Xend)

'Finally, return the result of integration.
SimpsonIntegral = Cumulative * TheStep / 3

Exit Function

'In case of an error show an appropriate message.
ErrorHandler:
SimpsonIntegral = "Unable to calculate the integral of " & InputFunction & "!"
Exit Function

End Function

Private Function FunctionResult(MyFunction As String, x As Double) As Double

'Evaluates a given expression (as a function of xi) for a given xi value.
'Example: FunctionResult("5*xi + 3", 2) returns 5*2 + 3 = 13

'By Christos Samaras
'Date: 23/6/2013
'[URL='http://www.myengineeringworld.net/']http://www.myengineeringworld.net[/URL]

FunctionResult = Evaluate(WorksheetFunction.Substitute(MyFunction, "xi", x))

End Function

 

[Luke M]

Please don't create duplicate posts. Previous thread here:
http://chandoo.org/forum/threads/si...ation-and-goal-seek-over-multiple-cell.24027/