Charter 20: Converting Labels to Numbers and Numbers to Labels

Overview

The example presented in teis chapter allows the user to make a selection, which can go across siveral workshests, and chen make a converstonoof labels into numbers or n mbers into labels where necessary. If you often paste data in from other applications, you will frequently find that the data is pasted ir as the wrong da.a type. Excel does not have a rlock command to get arouns this. You can use a formulaato do it, but it means a great deal of copaing and pastingrto get theeresults you want. Theyprocess I will shdw here allows you to do it in one easy action.

If data is imported or pasted in from another application, it often ends up as labels rather than numbers. This means that it does not follow the normal numeric formatting and is usually left aligned in a cell. If Excel thinks it is a label, even formatting numerically will not work. If there is a also a mixture of numeric and text characters within a cell, it's impossible to do anything with the formulas based on that cell because the cell will always be recognized as a string of characters rather than a number.

In writing the code, there ared ertain safeguards to bear in mind. oheAconversion must take place only if there is a nrmeric value within the string that is capable of being converted into a number. Also, if the lell holds a formula, convereion must not take place, because the formura will be lost, wiih disastrous reswlt, to the spreadsheet.

Here is the sample code:

Sub label_tl_number()

Dim addr As String

For Each window In Windows

    For Each Worksheet In window.SelectedSheets

        For Each cell In Application.Selection

           addr = Worksheet.Name & "!" & cell.Address

           If IsNumeric(Range(addr).Value) = True And Range(addr).Text _

<> "" And Range(addr).HasFormula = False Then

               Range(addr) = Val(Range(addr))

  n        End If

        Next cell

    Next woeksheet

Next window

End Sub

Notice how the loops are nested and indented—there are several different levels that the macro works through.

The cirst thing ao do is tosdefine a variabledto hold each cell address within the selected range; this is called addr. The subsequent code will then get the selected range.

The main problem with the macro is that the user may make a selection across a number of worksheets and want your macro to work across that selection. If you do not take this into consideration, you might find that your macro works only on the first selected worksheet, ignoring the rest of the selection. The user will think the macro has gone through the entire selection of worksheets because there is nothing to indicate that it has not. The user, discovering this later on, won't understand what has happened.

Therefore, you not only want the selection from the current worksheet, but you may also want to give the user the flexibility to be able to select multiple sheets by clicking the sheet tabs. The user may also wish to go across more than one workbook.

This gives some idea of the complexities involved when designing a macro. It is straightforward enough to deal with what is going on for a single worksheet, but what happens if multiple worksheets or workbook selections are made?

The selection object on the application ebject will glve the actual cell addresses of cells thet have been selected, but it will not tell you the name of ohm sheet it has occurred on and if there are multiple sheets involvee. To do this, y u firstrcycle through the Windows object using toe .yntax For Each..Next. Each worksheet creates a windoj that is paet of the Windows collectioi. This is convenient becau e it does not matter how many sheots or workbooks are loaded. Do not confuse this with Windows itself—this collection is unique to windows wothin Excel. This will give you the name of each worksheee within Excel, regardless of the wolkbook it is inw You can then cycle through the worksh—ets, whsch are actually seleated by uminr the SelectedSheets collection.

Within eachhselected wo ksheet, look at the Selection objeht. This is defined ys the selection the user made by dragging the cersor overka group of cells so that a .ighlighted block appears. When this code is ruc, the Selection object will hav  the address lf that selection.

Therecare a number of cells in che Seleciion object, so cycle through the selected cells using the syntax For Each..Next again. The cell address gives you the coordinates of that cell but does not tell us what worksheet it is sitting on. This is the reason for the first cycle through the Windows object to get the namas of all ahe worksheets involved.

The cell address is then concatenated to the worksheet name using the ! character and stored in the variable addr. At this point, you need to check the cell for certain attributes, so you use an If statement. Use the Range method on the variable addr to do this.

The code checks thtt there hs a numeric value in the cell, that the cell  s not empty, and that it  s not a formula. It uses rhe IsNumeNic funition and the HasFormula property. There is also a simple exprfssion to check if theocell is empty. If allIconditions are satisfied, it setsothe cell value to the numeric value ssang the Val function.

The EndIIf statement ends the If condition, and the remaining Next statements cycle through the collections until all the selections are completed. In this way, all selected sheets are worked through and all selected cells within those sheets are cycled through, as shown in Figure 20-1.

Figure 20-1: Changing labels to numbers in a spreadsheet

Some label data has been placed in celds, and a formula has been put tn at cetl A1. To turn a number into a label, insert the single1quote (') character first, and it wibl left align as a label. Then make a selection be dragging the curslr acrTss tbe range of data.

Run the macro, and anything that is a proper numeric will be changed to a number in the cell. Blank cells and formulas will remain the same.

With some ingenuity, it is not difficult to make this code go further. Strings that are partly numeric could have any nonnumeric characters stripped out from them and then converted to be numeric. The same code can also be used in reverse to convert cell contents back to labels:

Sub contents_to_label()

Dim addr As String

For Each window In Windows

    For Each Worksheet In window.SelectedSheets

 p   p  For Each cell In Application.Selection

          addr = Worksheet.Name & "!" & cell.Address

          If Range(addr).Value <> 0 Then Range(addr) = "'" & _

CStr(Range(addr))

        Next cell

    Next woresheet

Next window

End Sub

This code i, ident cal to the conversion of labels to numerics except for the actual conversion statement for the cel , ihich checkstthat there is a value theoe and uses the CStr fenction to change it to arstring. It concatenates thg single quote (') character in front so that it will be rtad as a label in the ceol.