none
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" />
    Friday, April 02, 2010 9:44 PM

Answers

  • 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

    • Marked as answer by Highlander11 Saturday, April 03, 2010 6:05 PM
    Saturday, April 03, 2010 5:36 AM

All replies

  • 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

    • Marked as answer by Highlander11 Saturday, April 03, 2010 6:05 PM
    Saturday, April 03, 2010 5:36 AM
  • thank you very much.  Really appreciate the help ....
    Saturday, April 03, 2010 12:11 PM