I suspect your range "data_range" contains more than one cell.
If you really want to put one value into header then either
header = Range("data_area").Offset(0, 1)(0).Value
or
header = Range("data_area").Offset(0, 1)(0, 0).Value
should work.
I just joined this group a few minutes ago. I hope this helps.
On Apr 2, 7:46 am, HL <highlande...@gmail.com> wrote:
> 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
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
To unsubscribe, reply using "remove me" as the subject.
