vba GOAL SEEK

  • Thread starter Thread starter Paulo
  • Start date Start date
P

Paulo

Hi,
I would like to make a command.button that runs the goal.seek formula for me.

I have a cell that has the total value for a series of equasions.
lets call it "TOTAL" and it usualy is a number like 543.23

Some times i need it to round it to 543, but not aways. and i cant just
round it. the value must come out from somewere. there is were the goal.seek
come in.

so, the goal.seek formula in VBA is
goalseek(Goal,ChangingCell As Range)As Boolean

and my curent formula for the button is
Private Sub CommandButton1_Click()
Sheets("ADITIVO").Select
Range("E57").Select
ActiveCell.FormulaR1C1 = "=ROUND(RC[-2],0)"
Cells(57, 5) = i
Range("C57").GoalSeek Goal:=i, ChangingCell:=Sheets("GERAL").Range("M6")

End Sub

when I hit the button, the result is always "ZERO"
I wich someone could help me figure out how do I substitute the valeu "i"in
the formula for the value inside the E57 cell that has the TOTAL rounded and
it is the valeu that i am expecting.
 
Hey Paulo -
Quick fix!

Change this line:
Cells(57, 5) = i
Click to expand...

To this:
i = Cells(57,5).value

What you have on the left of the equation sign gets the value that is on the
right-hand side of the equals sign. Before you were changing Cell E57 to the
value of i, which is zero since you hadn't given it a value yet, and that
would be why you were getting zero.

Not too bad, eh?

Hope this helps, Jim
 
Back
Top