vba GOAL SEEK

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

J Sedoff

Hey Paulo -
Quick fix!

Change this line:
Cells(57, 5) = i

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top