I may not have time to do this myself, so I'm hoping somebody out there sees this as a small project. The attached spreadsheet also provides a description, as follows:
I need an Excel VBA macro that finds a solution for each row before moving to the next row.
Here's how it should work:
I enter an initial "Guess" and an incrementing value ("Increment") to produce a "Test Value". That Test Value is then tested in the "Solution" column, starting in the first row. The macro does NOT interact directly with the Calculation Table; instead, it acts only on columns A, B, and C. As shown in the attached spreadsheet, the Test Value is used to calculate the values in the Calculation Table (columns E through N). The sum of those values are calculated in column B in "SUM".
The Test Value in the Solution column is incremented (by the positive Increment amount) until the Sum exactly equals the Target.
When Sum = Target, then that row is completed, and the function moves to the next row. The initial Test Value in the next row is the Solution value of the prior row plus the Increment. The process is repeated again until the Sum = Target. The process then moves to the next row and repeats until all the rows are completed.
EXAMPLE of logic:
If Bx < Ax Then
Add Increment to Test_Value [repeat this iteration until Bx is NOT less than Ax]
Else
If Bx = Ax Then
[go to next row and try Solution = (Test_Value + Increment) ]
Else [show "Error" in Solution]
Note: this "Error" should happen only if the Sum value skips over the Target (i.e., it jumps from < Target to > Target). If that happens then that means that I need to try a smaller Increment value.
Greetings!
FYI, this project should take an hour to complete at the most.
Also, I would also suggest having an error message show up near the "Initial Guess" and "Increment" fields, so that you don't have to scroll through the entire workbook to see if there is an error.
Thanks, and good luck!
Leslie
$30 USD in 3 days
0.0 (0 reviews)
1.0
1.0
9 freelancers are bidding on average $42 USD for this job