Problem using names as integer type

[Guest]

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)

 

[Guest]

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.

 

[Tom Ogilvy]

Dim tempVal as Long ' or Integer if you prefer

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