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

Percent format in input box

Thomas Kuriakose

Active Member
Respected Sirs,

We have a requirement to use goal seek using vba to arrive at the percent change based on input value and final value. Kindly find attached a sample sheet where the details are entered.

Cell E4 is the set cell
Cell C5 is the changing cell

When we enter the value in the input box as % or decimal value, it is not correctly calculating. How can we input a value of 0.05, 0.10 etc. or 5%, 10% etc. in the input box to get the result..
The code entered is as follows -
Code:
Option Explicit
Sub GoalSeekVBA()

Dim Target As Long

On Error GoTo Errorhandler

Target = InputBox("Enter the required value", "Enter Value")

Worksheets("Sheet1").Activate

With ActiveSheet

.Range("E4").GoalSeek Goal:=Target, ChangingCell:=Range("C5")

End With

Exit Sub


Errorhandler:

MsgBox ("Sorry, value is not valid.")

End Sub

Thank you very much for your support always,

with regards,
thomas
 

Attachments

  • Input value GS.xlsm
    15.7 KB · Views: 8
Hi Thomas !​
As an input box returns a String but your variable is Long (and 5% aka 0.05 can't be a Long type !)
so an error raises as you can check yourself without any error handler (useless here !) …​
As it returns an empty string if cancelled the only type variable is Variant combined with the Evaluate method​
whatever if the entry is "5%" or any decimal value …​
So without any error handler :​
Code:
    Dim V
        V = Evaluate(InputBox("Enter the required value", "Enter Value"))
        If IsError(V) Then Exit Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Hi Thomas !​
As an input box returns a String but your variable is Long (and 5% aka 0.05 can't be a Long type !)
so an error raises as you can check yourself without any error handler (useless here !) …​
As it returns an empty string if cancelled the only type variable is Variant combined with the Evaluate method​
whatever if the entry is "5%" or any decimal value …​
So without any error handler :​
Code:
    Dim V
        V = Evaluate(InputBox("Enter the required value", "Enter Value"))
        If IsError(V) Then Exit Sub
Do you like it ? So thanks to click on bottom right Like !​

Respected Sir,

Thank you so much for the great insight for this query.

This worked perfectly.

Thanks once again,

with regards,
thomas
 
Back
Top