Hi everyone,
I need to solve a mathematical problem by numerical methods, unfortunately, I am still new to VBA and the Solver add-in has been of no use to me.
I have 3 possible variables, each variable is a percentage (in actual fact I have 12 variables, 3 just looks neater in the code below). Sometimes I have less than this. As a result, I might need 3 For...Loops or I might need 1.
I need to run through every combinationof variables, until a condition on my spreadsheet is satisfied. For example, cell A1 = 0
Below is essence of my idea, but it doesn't work as it keeps jumping between the loops. The array intIsPercetangeZero() tells me how many variables there are.
I'm not sure if this is possible on VBA as it is. Is there a way to do this using a recursive function?
Thanks in advance!
[vba]
Dim intStart (1 to 3) as integer
Dim intEnd(1 to 3) as integer
Dim intIsPercetangeZero(1 to 3) as integer
Dim p1 as integer
Dim p2 as integer
Dim p3 as integer
Dim intMyInteger as integer
For intMyInteger = 1 to 3
If intIsPercetangeZero(intMyinteger) = 0 Then
intStart(intMyInteger)=0
intEnd(intMyInteger)=0
Else
intStart(intMyInteger)=0
intEnd(intMyInteger)=100
End If
Next intMyInteger
For p1 = intStart(1) to intEnd(1)For p2 = intStart(2) to intEnd(2)
For p3 = intStart(3) to intEnd(3)
If (p1/100)+(p2/100)+(p3/100) = 1 Then 'The sum of percentages must always equal 100%
Range("B1").value = p1/100
Range("C1").value = p2/100
Range("D1").value = p3/100
If Range("A1").value = 0 Then
Exit For
End If
End If
Next p1
[/vba]