How to add spaces before a number?

Asked

Viewed 48 times

0

I’m trying to add spaces before numbers. The first code worked, 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 loop below. But he enters loop infinite, because the VBA does not understand that the number of characters 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

Segue exemplo do resultado pretendido com o loop

vba loop

  • Daniel, sorry for the question that probably won’t be valid, but you can format the cells to be aligned right, would be the case?

2 answers

2

Thank you for paying attention to the code.

Evert: I tried to do it but it still didn’t work.

Leandro: Really the 3 If’s are simpler and there is 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 change from Do Until Len(numero) > 3 for Do While Len(numero) > 3. Strange that it is Do Until Len(numero) = 3 not right...

Hugs!

0

Hi, so if it’s only for up to 3 characters, I think it’s 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 the Do Until for a possible expansion in the number of characters I can also do.

Browser other questions tagged vba loop

You are not signed in. Login or sign up in order to post.