none
Excel VBA to List names with format RRS feed

  • Question

  • Hi All,

    In my workbook there are nearly 600 names. The below code list all name range in sheet 1 column A and cell refernece to that name range in column B. I then use excel text to column in column B to get all the values for those reference in column B.

    The issue is when i get value i don't get values with format.

    For e.g if name range "MYNAME" has value 1.00% than in column B i will get 0.01 which is number format.

    I need values in column B with format.

    Sub ListNames()
    Dim wks As Worksheet
    Set wks = Worksheets.Add
    wks.Range("A1").ListNames
    End Sub

    Thanks,

    Zaveri


    • Edited by zaveri cc Tuesday, August 19, 2014 7:58 PM
    Tuesday, August 19, 2014 5:57 PM

All replies

  • If you aren't sure where to start, the best thing is to record a macro so you start seeing the way Excel works. I recorded the following macro which is a framework for what you are asking:

    Sub Macro1()
    '
    ' Macro1 Macro
    '

    '
        Windows("Book1").Activate

        Range("A1").Select
        Selection.Copy
        Windows("Book2").Activate
        Range("E4").Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    End Sub

    Of course we can clean that up a bit:

    Sub Macro1()
        Windows("Book1").Activate

        Range("A1").Copy
        Windows("Book2").Activate
        Range("E4").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    End Sub

    Then add a loop to pull in your named ranges:

    Sub Macro1()

        For i = 1 to 600

             If Sheet2.range("A" & i).value <>"" then

        Windows("Book1").Activate

        Range(Sheet2.range("A" & i).value ).Copy
        Windows("Book2").Activate
        row(i).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False

    endif

    Next

    End Sub

    Of course you will need additional tweaking to get exactly what you want, but since you didn't supply any sample code of what you have already tried, I'm assuming you are unfamiliar with VBA and were looking for a starting point to work from.

    Tuesday, August 19, 2014 11:02 PM
  • Hi Zaveri,

    >>The issue is when i get value i don't get values with format.
    For e.g if name range "MYNAME" has value 1.00% than in column B i will get 0.01 which is number format.
    I need values in column B with format.<<

    As far as I know, we can get the formatted text via Range.Text or use the Range.Copy and Range.PasteSpecial.
    And Range.ListNames works well for me, here are my test steps:

    1. Create a name range and type value with 0.01 and format it with '%'
    2. Create a name range ant type value with 1%
    3. Run the Range.ListNames method and the it copy the name range with format successfully

    I tested the code in Excel 2007, Excel 2010 and Excel 2013. Did I miss any steps?

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 28, 2014 1:47 AM
    Moderator
  • Hello,

    Maybey I'm not seeing it right, but shouldn't you just adjust the numberformat property?

    for example:


    Range("test2").Value = Range("test").Value  'value will be 0.01
    Range("test2").NumberFormat = Range("test").NumberFormat  'format changes to percent and cell shows 1%

    Thursday, August 28, 2014 7:14 AM
  • I then use excel text to column in column B to get all the values for those reference in column B.

    The issue is when i get value i don't get values with format.

    I need values in column B with format.

    Sub ListNamesAndFormattedValues()
      Dim R As Range
      Worksheets.Add
      With Range("A1")
        .ListNames
        For Each R In .CurrentRegion.Columns(1).Cells
          R.Offset(, 2).NumberFormat = Range(R).NumberFormat
          R.Offset(, 2).Value = Range(R).Value
        Next
      End With
    End Sub


    Thursday, August 28, 2014 10:04 AM