• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code to change certain cell value

Y

Yash0071

New Member
Hello,

I have a sheet which is full of mathematical formulas. E.g., costs, revenues, interest etc. like in the financial statement.

In cell B12 I have sales markup. If I change this, Cell E12 becomes zero. The problem is it needs optimized markup e.g., 12.00678978340876834% and just 12% won't suffice. Can someone please help me with a code where upon its execution, the code will automatically set a markup e.g., 120.0965735246876984% which will make Cell E12 equal to 0? (Cell E12 usually has negative or positive number and sets to zero only with optimal markup) Note: the entire sheet is linked with plus or minus formula.

Regards.
 

Yash0071​

Is there any formula or something else in cell E12?
... or it's possible that after B12 change - it's value (E12) will force to zero.
You wrote about two %-values
... what are connections with those B12 and/or E12?
Later, You wrote that E12 has usually a number
... then that should mean
... ... there is not any formulas, but ... hmm? how do it sets to zero?
Some details are missing.
 
Code: 
Sub GenerateRandomOptimizedMarkup()
    Dim rngB12 As Range
    Dim optimizedPercentage As Double
    
    ' Generate a random optimized percentage (between 5% and 20%)
    Randomize
    optimizedPercentage = Rnd() * 0.15 + 0.05
    
    ' Set the value in cell B12
    Set rngB12 = Range("B12")
    rngB12.Value = optimizedPercentage
    
    ' Ensure cell E12 formula remains unchanged
    On Error Resume Next
    If Not IsEmpty(Range("E12").Formula) Then
        rngB12.Offset(0, 3).Formula = Range("E12").Formula
    End If
    On Error GoTo 0
End Sub

Thank you for getting back in short notice. This code is working but not efficiently. Your correct!! Only B12 does not consists of a formula. Rest of the sheet if fully linked like in the attached file. I used Frontline solver for optimization (E12=0) and other constraints, it worked like alchemy. Now I'm trying the same project with coding. If I change B12 cell= E12 must be zero; I12 cell must be >=0; K12 cells must be >=0. Note: even if errors are there in a model, Frontline Solver optimized it. Later on I rectified the errors and it still optimized. Based on that assumption, I left few items unattended in this excel.

Nice day.
 

Attachments

  • 3.1.xlsx
    29.8 KB · Views: 5

Yash0071​

... hmm?
Your original writing gives one kind of image.
Your ... code ... makes wondering
- why there are so many lines ... and with rnd()?
- what is connection with Your original writing.
Your 2nd writing gives ... another kind of image.
When do You run Your code?
 
I'm using those online code generators. It is taking a lot of time for me to understand what Function to use for heading and that's why I'm generating improper codes. If you have any idea as to what functions to use e.g., Option Explicit, Optimization etc., please suggest.
 

Yash0071​

I see ... You use copy & paste from some AI ... Okay.
Could You (only You) even try to answer to my the last question?
and
What should happen while running something?

Do You have a solution or something,
... what could be that B12 value which sets E12 to zero?
I tested this something 'my way' and ... there were some challenges.
 

Yash0071​

Is it true that You can skip all questions?
... without answers, there are challenges to help.
I'll skip links to somewhere. I got already an image ... how it should work.
I could make own code, which could give a result ... if there could be possible to find a result.
You used term easily.
... if someone uses term easy ... it'll mean too many times double or much more work.
Can You understand Yourself - how do it work?
... Are You sure that Your ... file ... works?
Because You wrote if it didnt work no problem.
 
OK! for the moment you can suspend it. I will remodel everything and make it easy to ask in a way that everyone can understand. Of course that is a working model. I know that works because I have published it online as well. 100% compliant.
 
Still skipping.
As You wrote:
Of course that is a working model.
I know that works because I have published it online as well.
100% compliant.
... then of course, You Can give valid, expected, correct value for B12?
 
Yes, that's what the code must find. The true optimized markup in B12 cell must be like 100.0875223567889% or 12.5647800075334765% or 129876.53424457898876%. It must find the true markup by itself. Only optimization sets the E12 cell to 0. You can quit working on it if it's cumbersome to understand.
 
then of course, You Can give valid, expected, correct value for B12?
... any of those Your like values do not set E12 cell to 0.
I can paste Your sentence:
You can quit working on it if it's cumbersome to understand.
 
100% in B12 cell doesn't set E12 cell to zero.
3000% in B12 cell doesn't set E12 cell to zero.
5000% in B12 cell doesn't set E12 cell to zero.
15890.0233980914% in B12 cell sets E12 cell to zero.
 
You must log in or register to reply here.
Back
Top