Skip to content
The Chat Archive
gpt-4-0314· OpenAI WildChat-4.8M (AI2)

i have a formula in excel and i want that the production of that formula be max. write a…

  1. 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

  2. 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 Sub
    

    This 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.