VBA Syntax to evaluate contents of Formula cells

[Dennis]

Using XL 2003 & 97

Getting a runtime error '438' in the Sub line 8 below [If
WorksheetFunction.Value....]

The function used Include is also below.

What is the correct syntax to ascertain if the Value (I think myStr) of the
cell contains numeric data?

Sub myTest()
Dim myCell As Range
Dim myStr As String
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
For Each myCell In Selection
If myCell.HasFormula = True Then
myStr = myCell.Value
If WorksheetFunction.Value(Include(myStr, "1234567890")) <> 0
Then
myCell.Interior.ColorIndex = 8
End If
End If
Next
End Sub


Function Include(StrInput As String, IncChar As String) As String
'
' From Bernie Deitrick Excel MVP 11/17/2004
'
' INCLUDE( ) returns a string consisting of only those characters from
string
' that appear in characters_to_include, in the same order that they appear
in the string.
'
' INCLUDE(string,characters_to_include)
'
' Example from ACL:
' INCLUDE("123 any street","0123456789")
' ="123"

Dim i As Integer

Include = ""

For i = 1 To Len(StrInput)
If InStr(1, IncChar, Mid(StrInput, i, 1)) > 0 Then
Include = Include & Mid(StrInput, i, 1)
End If
Next i

End Function

 

[Dennis]

Additional info missed in above post:

Runtime error 438 = Object doesn't support this property or method

TIA Dennis

 

[George Nicholson]

Include() returns a string, possibly an empty string. I think you simply
need to test the string's length:

If Len(Include(myStr, "1234567890")) <> 0 Then
(an empty string should return 0. Anything else indicates a numerical value
within myStr.

To answer your other question: IsNumeric() can be used in VBA much like the
IsNumber worksheet function. You could also try to coerce strings into
numbers using CInt(), CDbl(), etc.

 

[Dennis]

George,

Thanks for your time & knowledge

Dennis

Include() returns a string, possibly an empty string. I think you simply
need to test the string's length:

If Len(Include(myStr, "1234567890")) <> 0 Then
(an empty string should return 0. Anything else indicates a numerical value
within myStr.

To answer your other question: IsNumeric() can be used in VBA much like the
IsNumber worksheet function. You could also try to coerce strings into
numbers using CInt(), CDbl(), etc.

--
HTH,
George Nicholson

Remove 'Junk' from return address.

Using XL 2003 & 97

Getting a runtime error '438' in the Sub line 8 below [If
WorksheetFunction.Value....]

The function used Include is also below.

What is the correct syntax to ascertain if the Value (I think myStr) of
the
cell contains numeric data?

Sub myTest()
Dim myCell As Range
Dim myStr As String
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
For Each myCell In Selection
If myCell.HasFormula = True Then
myStr = myCell.Value
If WorksheetFunction.Value(Include(myStr, "1234567890")) <> 0
Then
myCell.Interior.ColorIndex = 8
End If
End If
Next
End Sub


Function Include(StrInput As String, IncChar As String) As String
'
' From Bernie Deitrick Excel MVP 11/17/2004
'
' INCLUDE( ) returns a string consisting of only those characters from
string
' that appear in characters_to_include, in the same order that they
appear
in the string.
'
' INCLUDE(string,characters_to_include)
'
' Example from ACL:
' INCLUDE("123 any street","0123456789")
' ="123"

Dim i As Integer

Include = ""

For i = 1 To Len(StrInput)
If InStr(1, IncChar, Mid(StrInput, i, 1)) > 0 Then
Include = Include & Mid(StrInput, i, 1)
End If
Next i

End Function

 

[Jack Sons]

Dennis,

I tried your code, nice. But I don't grasp the use of it. Is the result that
all cells with a formula AND that return a number get color 8? If so, could
it perhaps be done with the VBA equivalent of
if(isformula and if value isnumber)
or is that what the UDF Include does? Please explain.

When a formula results in an error like #VALUE! the code halts with Error 13
(types don't match ore something like that, I translated from Dutch).
What should the code be to give these cells another color (color 16 for
example)?

Jack Sons
The Netherlands

 

[Dennis]

The purpose of the code is to pull the numeric information from a cell.

In this particular situation I was looking for information in "cells" thet
begins with variable length "Alpha" characters. In the middle are meaningful
numerics followed by additional mixed characters.

This seemed to be an efficient way to capture the numbers.

In the end, I am looking for a way to highlight the cell if a formula has
constants like =A1+B1+9999

or

=A1+123456+B!

See new post 11/18/2004
http://support.microsoft.com/newsgr...misc&mid=7d643304-9e48-4e9c-ac49-2360aff58243

Additional info missed in above post:

Runtime error 438 = Object doesn't support this property or method

TIA Dennis

Using XL 2003 & 97

Getting a runtime error '438' in the Sub line 8 below [If
WorksheetFunction.Value....]

The function used Include is also below.

What is the correct syntax to ascertain if the Value (I think myStr) of the
cell contains numeric data?

Sub myTest()
Dim myCell As Range
Dim myStr As String
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
For Each myCell In Selection
If myCell.HasFormula = True Then
myStr = myCell.Value
If WorksheetFunction.Value(Include(myStr, "1234567890")) <> 0
Then
myCell.Interior.ColorIndex = 8
End If
End If
Next
End Sub


Function Include(StrInput As String, IncChar As String) As String
'
' From Bernie Deitrick Excel MVP 11/17/2004
'
' INCLUDE( ) returns a string consisting of only those characters from
string
' that appear in characters_to_include, in the same order that they appear
in the string.
'
' INCLUDE(string,characters_to_include)
'
' Example from ACL:
' INCLUDE("123 any street","0123456789")
' ="123"

Dim i As Integer

Include = ""

For i = 1 To Len(StrInput)
If InStr(1, IncChar, Mid(StrInput, i, 1)) > 0 Then
Include = Include & Mid(StrInput, i, 1)
End If
Next i

End Function