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?

Use

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


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"TJI" wrote in message
...
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?

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" wrote in message
...
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?

Dim tempVal as Long ' or Integer if you prefer

tempVal = Evaluate(Worksheets("Sheet1").Range("Serial").Refe rsto)

--
Regards,
Tom Ogilvy


"TJI" wrote in message
...
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" wrote in message
...
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?