How do I add spaces before a number?

Question

How do I add spaces before a number?

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

I'm trying to add spaces before numbers. The first code worked fine, but I wanted to do it in the form of loop .

Sub numeros()

numero = Range("A2").Value
n = 3
    If Len(Range("A" & n).Value) = 3 Then
        numero = Range("A" & n).Value
    ElseIf Len(Range("A" & n).Value) = 2 Then
        numero = " " & Range("A" & n).Value
    ElseIf Len(Range("A" & n).Value) = 1 Then
        numero = "  " & Range("A" & n).Value
    End If
        Range("B" & n).Value = "|" & numero & "|"
End Sub

The closest I got was the loop below. But it goes into infinite loop , because VBA does not understand that the number of characters has passed 3.

Sub numeros2()

n = 1
numero = 1
espaço = 0
    Do Until Len(numero) = 3
        numero = Space(espaço) & numero
        espaço = espaço + 1
    Loop
    Range("B" & n).Value = "|" & numero & "|"
End Sub

loop vba

asked by anonymous 09.06.2017 / 02:58

2 answers

Bringing pre-selected radio button via CakePhp Linq to Entities x Lambda Expression

score 2 · Answer 1

Thank you for your attention to the code.

Evert: I tried to do this but it still did not work out.

Leandro: Actually the 3 If's are simpler and have no need to change. But I wanted to study ways to loop.

Fortunately, today I decided to think differently and made the following code that worked.

Sub numeros1()
Dim numero As String
n = 1
numero = Range("A1").Value
n2 = 0

Do Until Len(numero) > 3
    numero = Space(n2) & numero
    n2 = n2 + 1
Loop

End Sub

It also works if you switch from Do Until Len(numero) > 3 to Do While Len(numero) > 3 . Strange that being Do Until Len(numero) = 3 does not work ...

Hugs!

score 0 · Answer 2

Hi, so if it's only for up to 3 characters, I find it much simpler to write 3 if's like this:

Sub numeros2()

    Dim numero

    Rng = Columns(1).End(xlDown).Row

    For i = 1 To Rng

        numero = Cells(i, 1)

        If Len(numero) = 1 Then

            Range("B" & i) = "|  " & numero & "|"

        ElseIf Len(numero) = 2 Then

            Range("B" & i) = "| " & numero & "|"

        ElseIf Len(numero) = 3 Then

            Range("B" & i) = "|" & numero & "|"

        End If

    Next i

End Sub

Now if you want to use Do Until for a possible expansion in the number of characters I can do as well.