Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to Change Value to Label

    I often need a macro to cycle through a list of numbers and change them to labels (by inserting an apostrophe) OR change a list of values and change them to numbers (by removing the apostrophe).

    I need this for use in a Vlookup or in Data Validation and just selecting the range and changing the format doesn't work.

    Does anyone know how to write a macro to do this?
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Change Value to Label

    The following code will convert the numbers in the range A1:A10 to three digit text strings with a single quote in front of each.

    <pre>Dim oCCell As Range
    For Each oCCell In Range("A1:A10")
    oCCell.Value = "'" & Format(oCCell.Value, "000")
    Next oCCell
    </pre>

    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Change Value to Label

    Legare,
    Thanks, that works fine - but is there a way for it to just cycle thru a list until it reaches the bottom of the list. Or even cycle thru the selected cells?

    Also, how would you do the reverse? Change a label to a number. I have tried Search&Replace looking for the apostrophe but it always tells me it can't find it
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Change Value to Label

    The following code will remove the leading single quote from all of the cells in the current selection.

    <pre>Dim oCCell As Range
    For Each oCCell In Selection
    oCCell.Value = oCCell.Value
    Next oCCell
    </pre>


    The following code will remove the leading single quote from all of the cells in column A starting at A1 down to the first empty cell.

    <pre>Dim I As Integer
    I = 0
    While Range("A1").Offset(I, 0).Value <> ""
    Range("A1").Offset(I, 0).Value = Range("A1").Offset(I, 0).Value
    I = I + 1
    Wend
    </pre>

    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Change Value to Label

    Legare,
    Thanks again. The first macro removing the leading single quote from all of the cells in the current selection works like a charm.

    The second macro didn't work unless I changed all the references to "A1" to "B1". So then I changed it to read:
    Dim I As Integer
    I = 0
    While Selection.Offset(I, 0).Value <> ""
    Selection.Offset(I, 0).Value = Selection.Offset(I, 0).Value
    I = I + 1
    Wend

    With it worded this way it works its way down a column until it reaches a blank. Excellent!

    Now, I'm trying to figure out how to combine the Selection.Offset idea in the macro that changes values to labels. I have the following code:
    Dim I As Integer
    I = 0
    While Selection.Offset(I, 0).Value <> ""
    Selection.Value = "'" & Format(oCCell.Value, "000")
    I = I + 1
    Wend

    I'm thinking I can't say "Dim I as Integer" if I want to change it to a value.

    I also can't say "Format(oCCell.Value, "000")" because I haven't defined oCCell. But I can't figure out what to use in place of oCCell.

    Am I on the right track?
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Change Value to Label

    Since you never said what range (row and column) your values were in, I wrote the macros to work in column A. I stated in my first post that it worked on cells in the range A1:A10. I was implying, but never explicitly said, that if your values were in a different location you would have to change the references to the location where your data resided. This also applied to the second macro. It was written to work starting in cell A1 and working down until it found a blank cell. If your values were in column B, then you would have to change the references from A1 to whatever cell contained your first value.

    Your change will have the macro work its way down from the upper left corner of the current selection. When it reaches the bottom of the selection, it will not stop if it has not found a blank cell yet. That is fine if that is what you want. You just need to select the top cell you want to convert and start the macro. You just need to know this is what is going to happen. You can also use the following code to work your way through all of the cells in the current selection, even if they are in more than one column, or in a non contiguous selection.

    <pre>Dim oCCell as Range
    For Each oCCell in Selection
    oCCell.Value=oCCell.Value
    Next oCCell
    </pre>


    Your second macro is would work a little better if it looked like this:

    <pre>Dim I As Integer
    I = 0
    While Selection.Offset(I,0).Value <> ""
    Selection.Offset(I,0).Value = "'" & Format(Selection.Offset(I,0).Value,"000")
    I = I + 1
    Wend
    </pre>

    Legare Coleman

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Change Value to Label

    You're right, I wasn't clear where the cells were, and they can be most anywhere depending on the project.

    Thanks for your help and advise. The macros make a tedious job a lot easier!
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Change Value to Label

    Glad I could help.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •