Evaluate Function 2

[longhair]

morning all,

perhaps not enough coffee yet...
i'm trying to find out if a substring within a cell evaluates to an integer and have not been able to find the correct statement (or group of statements yet). anyone have any ideas?
what i currently have is:

If Mid(astring, 4, 1) = "-" Then
 If Evaluate(Int(Mid(astring, 5, 3))) Then
  'action code here
 End If
End If

thanks in advance.
regards,
longhair

 

[xlbo]

evaluates to an integer as opposed to what else ?

You could convert to number and trap an error if you are just trying to make sure there is no alpha in there.

Alternatively, try performing a ROUND operation on it and seeing if it comes to the same as the number itself so

if round(Mid(astring, 5, 3)) = mid(astring,5,3) then
'it is an integer
else
'it may be a string or a double
end if


Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[longhair]

xlbo,
thanks. i'll try your round suggestion.
it would evaluate to a string or an integer.
possibilities for the cell text are such:
SHI-393ST PA MB10
SHI-393ST ZD MB10
SHI-393ST ZF MB10
SHI-50BC MB25
SHI-50SS MB25
SHI-50ST ZF MB25
what i need to do is evaluate if positions 5 through 8 are an integer, then do something. if they are not an integer (as in examples 4-6) then do nothing.
regards,

longhair

 

[PHV]

You may consider the IsNumeric function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[earthandfire]

You could try:

If Mid(astring, 4, 1) = "-" Then
 If IsNumeric((Mid(astring, 5, 3)) Then
  'action code here
 End If
End If

Hope this helps.

 

[longhair]

PHV - earthandfire,
a star to each of you for the help. IsNumeric is what i was needing.
regards,
longhair

 

[xlbo]

Probably an easier way than this but the following works:

Function IsInt(TestStr As String) As Boolean
Dim TestVar As Integer

On Error GoTo errTrap

TestVar = CInt(TestStr)
IsInt = True
On Error GoTo 0
Exit Function

errTrap:
IsInt = False
On Error GoTo 0

End Function

Sub Check_Integers()
Dim aString As String

aString = Cells(1, 1).Text

If IsInt(Mid(aString, 5, 3)) = True Then
    MsgBox "String contains a 3 digit integer"
Else
    MsgBox "String does not contain a 3 digit integer"
End If

End Sub

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question