Excel VBA code how to add text to specific cell

Question

Length of all cells in a specific columns has to be 6 characters. If not, I have to add 0 in the beginning of each cell until cell length =6. What is the best way to do it?

Welcome to S.O! Have you tried anything? If so, please, provide the code, take a look to the tour and how to ask. Friendly reminder: StackOverflow is not a "we code for you" service provider. Introduction to VBA — Sgdva
– Sgdva, Commented Jul 15, 2016 at 15:04
Note that this can be done without VBA if you accept that the result is put in a different column. You can then hide the original column. If there is a choice, then it is almost always better to do such things with in-cell formulas than with VBA. — trincot
– trincot, Commented Jul 15, 2016 at 15:06
Assuming that the column you wish to reformat is in column A and starts in row 1 you could write in cell B1 the following formula and copy it down =RIGHT("000000"&MID(A1,1,6),6). — Ralph
– Ralph, Commented Jul 15, 2016 at 16:18

Benno Grimm · Accepted Answer · 2016-07-15 17:12:28Z

1

Dim lastRow As Integer
Dim i As Integer
Dim sh As Worksheet
Dim cont As String


Set sh = ThisWorkbook.Worksheets("Sheet1") 'Your Sheet

With sh
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'Coloumn A

    For i = 1 To lastRow
        Cells(i, 1).NumberFormat = "@"
        Do Until Len(Cells(i, 1)) = 6   'Coloumn A
            cont = Cells(i, 1)          'Coloumn A
            Cells(i, 1) = "0" & cont    'Coloumn A
        Loop
    Next i

End With

Does this work for you? You only have to edit the coloumn that you want to check, you could use a TextBox for that.

edited Jul 15, 2016 at 17:12

answered Jul 15, 2016 at 17:05

Benno Grimm

5401 gold badge9 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gary's Student · Accepted Answer · 2016-07-15 16:29:17Z

0

Here is a sample for column C:

Sub Make6()
    Dim r As Range, r1 As Range, r2 As Range, r3 As Range
    bry = Array("000000", "00000", "0000", "000", "00", "0", "")
    Dim N As Long, v As String, L As Long
    Set r1 = Range("C:C")
    N = Cells(Rows.Count, "C").End(xlUp).Row
    Set r2 = Range("C1:C" & N)
    Set r3 = Range("C" & N + 1 & ":C" & Rows.Count)

    r1.NumberFormat = "@"
    For Each r In r2
        v = r.Value
        L = Len(v)
        If L < 6 Then
            r.Value = bry(L) & v
        End If
    Next r
    r3.Value = "000000"

End Sub

It will fix ALL cells in column C.

answered Jul 15, 2016 at 16:29

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

Excel VBA code how to add text to specific cell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related