What is the problem with the following ?

Dim r As Integer
Dim s As String

r = Application.Count(Sheet1.Range("Set_Size_Range"))
s = ActiveWorkbook.Names("Set_Size").RefersTo

If r = s Then



When the IF line executes, a "Run-time error '13': Type mismatch" error
occurs.

The following are the debug.print outputs

r equals 126

s equals =ABS(MIN(Data_Header_Row))

If you type =Set_Size in a cell, the value returned is 126.

What is the syntax for assigning the value of the name "Set_Size" to the
variable s ?



kittronald

On Feb 24, 5:05*pm, "kittronald" wrote:
* * What is the problem with the following ?

* * * * Dim r As Integer
* * * * Dim s As String

* * * * r = Application.Count(Sheet1.Range("Set_Size_Range"))
* * * * s = ActiveWorkbook.Names("Set_Size").RefersTo

* * * * If r = s Then

* * When the IF line executes, a "Run-time error '13': Type mismatch" error
occurs.

* * The following are the debug.print outputs

* * * * r equals *126

* * * * s equals =ABS(MIN(Data_Header_Row))

* * If you type =Set_Size in a cell, the value returned is 126.

* * What is the syntax for assigning the value of the name "Set_Size" to the
variable s ?

kittronald

You may benefit from using
msgbox r
msgbox s
for testing

Don,

Thanks for the quick reply.

I did that and got the same results as the debug.print.

I can see that variable r is returning a number and variable s is
returning the formula (string) in the Refers to: field of the name.

How can I get the variable s to evaluate to the value of the name
"Set_Size" which is the number 126.


kittronald

s = ActiveWorkbook.Names("Set_Size").RefersTo
MsgBox Application.Evaluate(s)

Tim

On Feb 24, 3:23*pm, "kittronald" wrote:
Don,

* * Thanks for the quick reply.

* * I did that and got the same results as the debug.print.

* * I can see that variable r is returning a number and variable s is
returning the formula (string) in the Refers to: field of the name.

* * How can I get the variable s to evaluate to the value of the name
"Set_Size" which is the number 126.

kittronald

Tim,

Thanks for the response.

What I'm trying to do is get the variable s to equal the value that is
returned by the defined name "Set_Size".

The problem is the variable r is an integer and the Refers to: field of
the defined name "Set_Size" contains a formula (which makes it a string
value).

A type mismatch occurs because the IF statement is comparing two
different data types.

What's the best way to get the variable s to equal the integer value of
the "Set_Size" name ?


kittronald

kittronald brought next idea :
Tim,

Thanks for the response.

What I'm trying to do is get the variable s to equal the value that is
returned by the defined name "Set_Size".

The problem is the variable r is an integer and the Refers to: field of
the defined name "Set_Size" contains a formula (which makes it a string
value).

A type mismatch occurs because the IF statement is comparing two
different data types.

What's the best way to get the variable s to equal the integer value of
the "Set_Size" name ?


kittronald

Use a Variant data type to retrieve the value stored in the defined
name, with the Evaluate() function.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion