CODEWITHSUNDEEP
vbaexcel
F0l0w
F0l0w

Reputation: 261

Excel vba adding spaces to a cell to reach a certain length

I am trying to have the cells in the range A1 to A6 to reach the length of 18.

The current values in it:

A233333
A0399
30000
3993833
11111
22222

Note that not all the values in the cells have the same length.

I want to fill the remaining length with blank spaces (" "). So I wrote the following code but it does not seem to work:

Sub PMAP_BAC_Code()

Dim rCell As Range
Dim rRng As Range
Set rRng = Sheet1.Range("A1:A6")

For Each rCell In rRng.Cells
If Len(xCell) <> 18 Then
    xCell.FormulaR1C1 = " "
End If
Next rCell

End Sub

Can someone help me please?

Upvotes: 1

Views: 8503

Answers (3)

Stupid_Intern
Stupid_Intern

Reputation: 3450

Try this :

Sub PMAP_BAC_Code()

Dim str As String
Dim wk As Worksheet
Set wk = Sheet1

For i = 1 To 6
    str = wk.Range("A" & i).Value

    Do While Len(str) < 18
    str = str & " "
    Loop

    wk.Range("A" & i) = str
    str = ""

Next i


End Sub

If the col A has only numbers like 1 then above code will convert the string "1 " to "1" again so instead I use to concatenate with NBSP which is chr(160) which will take care of it

Sub PMAP_BAC_Code()

Dim str As String
Dim wk As Worksheet
Set wk = Sheet1

For i = 1 To 6
    str = wk.Range("A" & i).Value

    Do While Len(str) < 18
        str = str & Chr(160)
    Loop

    wk.Range("A" & i) = CStr(str)
    Debug.Print Len(wk.Range("A" & i))
    Debug.Print Len(str)
    str = ""

Next i


End Sub

Upvotes: 2

Vincent G
Vincent G

Reputation: 3188

Do not forget that Format can also be used to format Strings:

Public Function myFormat(str As String) As String
    myFormat = Format(str, "!" & String(18, "@"))
End Function

Edit: My function applied to the current problem:

Sub PMAP_BAC_Code()

    Dim rCell As Range
    Dim rRng As Range
    Set rRng = Sheet1.Range("A1:A6")

    For Each rCell In rRng.Cells
        rCell.NumberFormat = "@"
        rCell.Value = myFormat(rCell.Value)
    Next rCell

End Sub

Upvotes: 2

S Meaden
S Meaden

Reputation: 8260

Why not use Left$("foo" & String$(17," ") ,17)?

Sub PMAP_BAC_Code()

    Dim rCell As Range
    Dim rRng As Range
    Set rRng = Sheet1.Range("A1:A6")

    For Each rCell In rRng.Cells
    Const lMY_LENGTH As Long = 18
    If Len(rCell) <> lMY_LENGTH Then
        rCell.Value2 = IIf(IsNumeric(rCell.Value2), "'", "") & Left$(rCell.Value2 & String$(lMY_LENGTH, " "), lMY_LENGTH)
    End If
    Next rCell

End Sub

Folding in feedback from comment about numbers, I have added a single quote for numerics. Other comments about not lining up the end of the text are still true though.

Upvotes: 5

Related Questions