# Need to Give Goal Seek a Margin of Error in VBA

#### avd88

##### Board Regular
Hello,

Hoping someone can help me give goalseek a margin of error in VBA.

I created a spreadsheet that calculates how much you would pay on a loan if you are also getting a return (rent) on your investment.
My workbook has a couple of spreadsheets that use an amortization table, currency conversions (investment in another country), etc.

I added a vba button with goalseek that would tell me what is the maximum downpayment I can put in after all other variables are set (years, interest rate, rent, etc.) so that my loan payments are equal to my rent.
Because there are so many calculations going on the function sometimes works but most of the times it goes haywire. Because I don't need a very precise level of accuracy I think I can fix the issue by giving the goalseek a margin of error of +-1 or even +-5.

Cell O1 is equal to Rent - Loan Payment.
Goal is O1 = 0.
By changing Cell E2 which is equal to Downpayment.

This is my code:

VBA Code:
``````Private Sub GoalSeek_Click()
Range("O1").GoalSeek _
Goal:=0, _
ChangingCell:=Range("E2")

End Sub``````

Thanks!!

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### MrSyyr

##### New Member
Hello,

Hoping someone can help me give goalseek a margin of error in VBA.

I created a spreadsheet that calculates how much you would pay on a loan if you are also getting a return (rent) on your investment.
My workbook has a couple of spreadsheets that use an amortization table, currency conversions (investment in another country), etc.

I added a vba button with goalseek that would tell me what is the maximum downpayment I can put in after all other variables are set (years, interest rate, rent, etc.) so that my loan payments are equal to my rent.
Because there are so many calculations going on the function sometimes works but most of the times it goes haywire. Because I don't need a very precise level of accuracy I think I can fix the issue by giving the goalseek a margin of error of +-1 or even +-5.

Cell O1 is equal to Rent - Loan Payment.
Goal is O1 = 0.
By changing Cell E2 which is equal to Downpayment.

This is my code:

VBA Code:
``````Private Sub GoalSeek_Click()
Range("O1").GoalSeek _
Goal:=0, _
ChangingCell:=Range("E2")

End Sub``````

Thanks!!
You could try making say P1 the margin of error (=5) then make Q1 = Rounddown(O1/P1,0) which will be 0 within the error margin and make the goal seek a value of 0 for Q1.

• avd88

#### avd88

##### Board Regular
Hy MrSyyr, that little hack worked! Thanks, I had tried something outside of VBA yesterday but couldn't get it to work well. Thanks again, great input!

Replies
0
Views
529
Replies
11
Views
352
Replies
2
Views
575
Replies
4
Views
98
Replies
4
Views
201