Excel 2007 VBA convert variant() to string

Question

Hi All,

I am writing a VBA macro in Microsoft Excel 2007 and run into the
following problem

Due to back-compatibility reasons, I have to do define a named range
from C1 to E12 called "data_area".
I need to refer to this named range in my code

C1 to E1 are column names, e.g., Column1, Column2, Column3
C1 to E1 are pre-populated by another user.

I need to read column name from C1 to E1 into a VBA String variable
and then do some processing.
It is at this point I encountered a problem.

Dim header as String
header = Range("data_area").Offset(0, 1).Value
---===-- when running to the above line, I got a type mismatch error

I tried the following:
1.  print out the type name of  Range("data_area").Offset(0, 1).Value
Cells(1,1) =  TypeName( Range("data_area").Offset(0, 1).Value )
--===--- after running the above statement, Cell (A,1) displays
"variant()".  Note that there is a pair of () following variant.

2.  I tried to convert variant() to string using the following two
statement, but still got data type mismatch error.
header = CStr( Range("data_area").Offset(0, 1).Value )
or
header = Str( Range("data_area").Offset(0, 1).Value )

I am complete stuck, any help is highly appreciated.

HL <input id="gwProxy" type="hidden"></input> <input id="jsProxy" onclick="jsCall();" type="hidden" />

Answer 1

Actually Range("data_area").Offset(0, 1) means the range from D1 to F12.

If you want to get the cell of Range("data_area"), you can use below code:

Range("data_area").Cell(1,1).value    ' C1

Range("data_area").cells(1,2).value   ' D1

Range("data_area").cells(1,3).value   ' E1

Range("data_area").cells(2,1).value   ' C2

Answer 2

thank you very much.  Really appreciate the help ....