Problem using names as integer type

[TJI]

I have been trying to get some code working - the intent is to have the
program read through the column headers of a table and create a Worksheet
name to store the associated column name with the column number. This part of
the code works. But when I try to use the Worksheet name in the VBA code, I
keep getting a type mismatch.

'this assigns the Worksheet name of column 1 (happens to be "Serial") to the
integer value of 1:
Worksheets("Sheet1").Names.Add Name:=Worksheets("Sheet1").Cells(1, 1).Value,
RefersTo:=1

'this trys to access the column number to use in a calculation:
tempVal = Worksheets("Sheet1").Names("Serial").Value

tempVal is shown in the Locals as a variant/string and is equal to "=1",
which cannot be used in calculations.

Is there a way to get tempVal to be an integer which I can use in
calculations?

[Bob Phillips]

Use

tempVal = Worksheets("Sheet1").Range("Serial").Value


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"TJI" <[email protected]> wrote in message
news:[email protected]...
> I have been trying to get some code working - the intent is to have the
> program read through the column headers of a table and create a Worksheet
> name to store the associated column name with the column number. This part
of
> the code works. But when I try to use the Worksheet name in the VBA code,
I
> keep getting a type mismatch.
>
> 'this assigns the Worksheet name of column 1 (happens to be "Serial") to
the
> integer value of 1:
> Worksheets("Sheet1").Names.Add Name:=Worksheets("Sheet1").Cells(1,
1).Value,
> RefersTo:=1
>
> 'this trys to access the column number to use in a calculation:
> tempVal = Worksheets("Sheet1").Names("Serial").Value
>
> tempVal is shown in the Locals as a variant/string and is equal to "=1",
> which cannot be used in calculations.
>
> Is there a way to get tempVal to be an integer which I can use in
> calculations?

[TJI]

When I tried this, I got the following error:
"Run-time error'1004':
Application-defined or object-defined error"

If I go to the worksheet, I can type =Serial * 2 without an error ...

Tim.

"Bob Phillips" wrote:

> Use
>
> tempVal = Worksheets("Sheet1").Range("Serial").Value
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "TJI" <[email protected]> wrote in message
> news:[email protected]...
> > I have been trying to get some code working - the intent is to have the
> > program read through the column headers of a table and create a Worksheet
> > name to store the associated column name with the column number. This part
> of
> > the code works. But when I try to use the Worksheet name in the VBA code,
> I
> > keep getting a type mismatch.
> >
> > 'this assigns the Worksheet name of column 1 (happens to be "Serial") to
> the
> > integer value of 1:
> > Worksheets("Sheet1").Names.Add Name:=Worksheets("Sheet1").Cells(1,
> 1).Value,
> > RefersTo:=1
> >
> > 'this trys to access the column number to use in a calculation:
> > tempVal = Worksheets("Sheet1").Names("Serial").Value
> >
> > tempVal is shown in the Locals as a variant/string and is equal to "=1",
> > which cannot be used in calculations.
> >
> > Is there a way to get tempVal to be an integer which I can use in
> > calculations?
>
>
>

[Tom Ogilvy]

Dim tempVal as Long ' or Integer if you prefer

tempVal = Evaluate(Worksheets("Sheet1").Range("Serial").Refersto)

--
Regards,
Tom Ogilvy


"TJI" <[email protected]> wrote in message
news:[email protected]...
> When I tried this, I got the following error:
> "Run-time error'1004':
> Application-defined or object-defined error"
>
> If I go to the worksheet, I can type =Serial * 2 without an error ...
>
> Tim.
>
> "Bob Phillips" wrote:
>
> > Use
> >
> > tempVal = Worksheets("Sheet1").Range("Serial").Value
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "TJI" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have been trying to get some code working - the intent is to have
the
> > > program read through the column headers of a table and create a
Worksheet
> > > name to store the associated column name with the column number. This
part
> > of
> > > the code works. But when I try to use the Worksheet name in the VBA
code,
> > I
> > > keep getting a type mismatch.
> > >
> > > 'this assigns the Worksheet name of column 1 (happens to be "Serial")
to
> > the
> > > integer value of 1:
> > > Worksheets("Sheet1").Names.Add Name:=Worksheets("Sheet1").Cells(1,
> > 1).Value,
> > > RefersTo:=1
> > >
> > > 'this trys to access the column number to use in a calculation:
> > > tempVal = Worksheets("Sheet1").Names("Serial").Value
> > >
> > > tempVal is shown in the Locals as a variant/string and is equal to
"=1",
> > > which cannot be used in calculations.
> > >
> > > Is there a way to get tempVal to be an integer which I can use in
> > > calculations?
> >
> >
> >