IsNumber is not working

Hi,

Can anyone identify the error in my looping? My IsNumber(f) is not working. If I enter a string, an error message will pop up, but if I enter a number (e.g 4 or -4), the same error message pops up too...what can I do?

I cant really set the inputbox to Type:=1, because I want this inputbox to be optional to the user, i.e If user does not enter anything, it will return a 2.

any help will be much appreciated!

Thanks!

Please Login or Register  to view this content.

Isn't this the same problem that you asked and got answered yesterday?

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"korokke" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> Can anyone identify the error in my looping? My IsNumber(f) is not
> working. If I enter a string, an error message will pop up, but if I
> enter a number (e.g 4 or -4), the same error message pops up too...what
> can I do?
>
> I cant really set the inputbox to Type:=1, because I want this inputbox
> to be optional to the user, i.e If user does not enter anything, it will
> return a 2.
>
> any help will be much appreciated!
>
> Thanks!
>
>
> Code:
> --------------------
> Sub frequency2()
>
>
> 'Get the frequency of coupon payments per year
>
> frequency = Application.InputBox("Please enter the frequency of the
coupon payments", _
> "Frequency of the coupon payments", , , , , 1)
>
> 'If c = 0 Then 'If it is a zero-coupon bond, set the frequency of coupon
payments to 0
> 'frequency1 = 0
> 'Debug.Print frequency1
> 'ElseIf frequency = False Then
> 'frequency1 = 0
> 'Else
> frequency1 = determinefrequency(frequency)
> Debug.Print frequency1
> 'End If
> End Sub
>
> ' This function automatically sets the value 2 to the Frequency of
Coupon Payment of the Bond if the user
> ' did not enter anything when prompted.
> ' It also validates if the user entered negative numbers
> Function determinefrequency(Optional ByVal f As Variant) As Variant
>
> Dim testt As Boolean
>
> Do
> ' If values have been entered into the inputbox and
> ' the userinput is a number
> If Len(f) > 0 And WorksheetFunction.IsNumber(f) Then
>
>
> Do
> 'if frequency of coupon payment is valid
> If f = 0 Or f = 1 Or f = 2 Or f = 4 Then
> testt = True
> determinefrequency = f
> Debug.Print f
>
> 'if frequency of coupon payment is negative
> ElseIf f < 0 Then
> testt = False
> MsgBox "Frequency of coupon payment needs to be positive", vbCritical,
"Warning"
>
> f = Application.InputBox("Please enter the frequency of the coupon
payments", _
> "Frequency of the coupon payments", , , , , 1)
>
> 'if frequency of coupon payment is not equal to 0, 1, 2 or 4
> Else
> testt = False
> MsgBox "Frequency of coupon payments needs to be 0 or 1 or 2 or 4",
vbCritical, "Warning"
>
> f = Application.InputBox("Please enter the frequency of the coupon
payments", _
> "Frequency of the coupon payments", , , , , 1)
> End If
> Loop Until testt
> ElseIf Len(f) > 0 And Not WorksheetFunction.IsNumber(f) Then 'If text
has been entered
> testt = False
> MsgBox "Frequency of coupon payments is invalid", vbCritical, "Warning"
> f = Application.InputBox("Please enter the frequency of the coupon
payments", _
> "Frequency of the coupon payments", , , , , 1)
> 'End If
> 'Loop Until testt
>
> ' if values have been entered into the inputbox but
> ' the user input is not a number
> 'ElseIf Len(f) > 0 And Application.IsNumber(f) = False Then
> 'MsgBox "Frequency of coupon payment needs to be positive", vbCritical,
"Warning"
>
> 'f = Application.InputBox("Please enter the frequency of the coupon
payments", _
> '"Frequency of the coupon payments", , , , , 1)
> Else
> determinefrequency = 2
> testt = True
> End If
> Loop Until testt
>
> End Function
> --------------------
>
>
> --
> korokke
> ------------------------------------------------------------------------
> korokke's Profile:
http://www.excelforum.com/member.php...o&userid=34760
> View this thread: http://www.excelforum.com/showthread...hreadid=546249
>

try isnumeric

Sub ABC()
Dim Frequency As String
Dim msg As String
msg = "Please enter the frequency of the coupon payments" & _
vbNewline & "(should be 2, 4, 6, or 8)"
TryAgain:
Frequency = InputBox(msg, _
"Frequency of the coupon payments")
If StrPtr(Frequency) = 0 Then
' User hit Cancel, so exit
Exit Sub
End If
' use hit OK with no entry or entered something
If Frequency <> "" Then
' user entered something
If Not IsNumeric(Frequency) Then
' entry isn't numeric
msg = "Please enter a number or nothing"
GoTo TryAgain
Else
If Frequency <= 0 Then
' entry is not positive
msg = "Frequency should be positive"
GoTo TryAgain
ElseIf Frequency > 8 Then
' entry is greater than 8
msg = "Frequency should be 2, 4, 6, or 8"
GoTo TryAgain
ElseIf Frequency Mod 2 <> 0 Then
' entry isn't divisible by 2
msg = "Frequency should be 2, 4, 6, or 8"
End If
End If
Else
' user hit OK with no entry
Frequency = 2
End If

End Sub

--
Regards,
Tom Ogilvy

"korokke" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> Can anyone identify the error in my looping? My IsNumber(f) is not
> working. If I enter a string, an error message will pop up, but if I
> enter a number (e.g 4 or -4), the same error message pops up too...what
> can I do?
>
> I cant really set the inputbox to Type:=1, because I want this inputbox
> to be optional to the user, i.e If user does not enter anything, it will
> return a 2.
>
> any help will be much appreciated!
>
> Thanks!
>
>
> Code:
> --------------------
> Sub frequency2()
>
>
> 'Get the frequency of coupon payments per year
>
> frequency = Application.InputBox("Please enter the frequency of the
coupon payments", _
> "Frequency of the coupon payments", , , , , 1)
>
> 'If c = 0 Then 'If it is a zero-coupon bond, set the frequency of coupon
payments to 0
> 'frequency1 = 0
> 'Debug.Print frequency1
> 'ElseIf frequency = False Then
> 'frequency1 = 0
> 'Else
> frequency1 = determinefrequency(frequency)
> Debug.Print frequency1
> 'End If
> End Sub
>
> ' This function automatically sets the value 2 to the Frequency of
Coupon Payment of the Bond if the user
> ' did not enter anything when prompted.
> ' It also validates if the user entered negative numbers
> Function determinefrequency(Optional ByVal f As Variant) As Variant
>
> Dim testt As Boolean
>
> Do
> ' If values have been entered into the inputbox and
> ' the userinput is a number
> If Len(f) > 0 And WorksheetFunction.IsNumber(f) Then
>
>
> Do
> 'if frequency of coupon payment is valid
> If f = 0 Or f = 1 Or f = 2 Or f = 4 Then
> testt = True
> determinefrequency = f
> Debug.Print f
>
> 'if frequency of coupon payment is negative
> ElseIf f < 0 Then
> testt = False
> MsgBox "Frequency of coupon payment needs to be positive", vbCritical,
"Warning"
>
> f = Application.InputBox("Please enter the frequency of the coupon
payments", _
> "Frequency of the coupon payments", , , , , 1)
>
> 'if frequency of coupon payment is not equal to 0, 1, 2 or 4
> Else
> testt = False
> MsgBox "Frequency of coupon payments needs to be 0 or 1 or 2 or 4",
vbCritical, "Warning"
>
> f = Application.InputBox("Please enter the frequency of the coupon
payments", _
> "Frequency of the coupon payments", , , , , 1)
> End If
> Loop Until testt
> ElseIf Len(f) > 0 And Not WorksheetFunction.IsNumber(f) Then 'If text
has been entered
> testt = False
> MsgBox "Frequency of coupon payments is invalid", vbCritical, "Warning"
> f = Application.InputBox("Please enter the frequency of the coupon
payments", _
> "Frequency of the coupon payments", , , , , 1)
> 'End If
> 'Loop Until testt
>
> ' if values have been entered into the inputbox but
> ' the user input is not a number
> 'ElseIf Len(f) > 0 And Application.IsNumber(f) = False Then
> 'MsgBox "Frequency of coupon payment needs to be positive", vbCritical,
"Warning"
>
> 'f = Application.InputBox("Please enter the frequency of the coupon
payments", _
> '"Frequency of the coupon payments", , , , , 1)
> Else
> determinefrequency = 2
> testt = True
> End If
> Loop Until testt
>
> End Function
> --------------------
>
>
> --
> korokke
> ------------------------------------------------------------------------
> korokke's Profile:
http://www.excelforum.com/member.php...o&userid=34760
> View this thread: http://www.excelforum.com/showthread...hreadid=546249
>