Solved

routines dropping leading zeros

Posted on 2013-06-28
2
225 Views
Last Modified: 2013-07-03
excel 2010 vba...

I have the following routines deleting leading zeros for some reason...

I NEED TO KEEP my leading zeros

Sub ConvertCase()

Dim rAcells As Range, rLoopCells As Range

Dim lReply As Long



    'Set variable to needed cells

    If Selection.Cells.count = 1 Then

        Set rAcells = ActiveSheet.UsedRange

    Else

       Set rAcells = Selection

    End If





    On Error Resume Next 'In case of NO text constants.

    'Set variable to all text constants

    Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)

   

    If rAcells Is Nothing Then

       On Error GoTo 0

       Exit Sub

    End If

         

            For Each rLoopCells In rAcells

              rLoopCells = StrConv(rLoopCells, vbUpperCase)

          Next rLoopCells

 End Sub


Sub convertupper()
    Dim rng As Range, cell As Range
    Dim lrow As Long

    lrow = Cells(Cells.Rows.count, "I").End(xlUp).Row

    Set rng = Range("I1:I" & lrow)

    For Each cell In rng
        cell.Value = UCase(cell.Value)
    Next cell

End Sub
0
Comment
Question by:fordraiders
2 Comments
 
LVL 2

Accepted Solution

by:
arildj78 earned 500 total points
ID: 39286015
In Sub ConvertCase() change rLoopCells = StrConv(rLoopCells, vbUpperCase) to
If Not IsNumeric(rLoopCells) Then
     rLoopCells = StrConv(rLoopCells, vbUpperCase)
End If

Open in new window

In Sub convertupper() change cell.Value = UCase(cell.Value) to
If Not IsNumeric(cell.Value) Then
     cell.Value = UCase(cell.Value)
End If

Open in new window

This should make sure that cells with numbers are left alone. Another option is to insert a ' in front of the number if it contains leading zeros. This can done like this
If IsNumeric(cell.Value) And Left(cell.Value, 1) = "0" Then
    cell.Value = Chr(39) & UCase(cell.Value)
Else
    cell.Value = UCase(cell.Value)
End If

Open in new window

0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 39298132
Thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question