i have a formula in excel and i want that the production of that formula be max. write a…
- User
i have a formula in excel and i want that the production of that formula be max. write a vba that by changing variable find the max for function.dont use solver.the variable cells are in am3 to ao3.and target cell is in ap3
- Assistant
You can use a simple VBA loop to test different combinations of values to find the maximum value for your function. Here's an example of how to do it:
Sub FindMax() Dim i As Integer, j As Integer, k As Integer Dim Am3Val As Variant, Ao3Val As Variant, Ap3Val As Variant Dim MaxValue As Double ' Set initial max value to a very low number MaxValue = -1 * 10 ^ 6 ' Loop through possible values for each variable For i = 1 To 100 For j = 1 To 100 For k = 1 To 100 ' Set values of the variable cells Range("Am3").Value = i Range("An3").Value = j Range("Ao3").Value = k ' Calculate target cell value Ap3Val = Range("Ap3").Value ' If new value is greater than current max, update the max value If Ap3Val > MaxValue Then MaxValue = Ap3Val Am3Val = i An3Val = j Ao3Val = k End If Next k Next j Next i ' Set the variable cells to the values that result in the max value Range("Am3").Value = Am3Val Range("An3").Value = An3Val Range("Ao3").Value = Ao3Val MsgBox "Found Max: " & MaxValue & " (" & Am3Val & ", " & An3Val & ", " & Ao3Val & ")" End SubThis code will loop through all possible values (from 1 to 100) for the variables Am3, An3, and Ao3, and will keep track of the maximum value found for Ap3. After the loop, it sets the variable cells to the values that gave the maximum result.
This is a brute-force approach and can be slow for larger ranges or more variables, but it should work for simple cases. You can adjust the ranges (1 to 100) to better match your problem, and you may need to adjust the step size (e.g., use "i = i + 0.5" instead of "i = i + 1") depending on how precise you want the results to be.