left padding (with given character)

[acolbrantNMBS]

Hi Folks

For each cell in a selection I would want the following executed:
set the cell format to 'text'
select the needed length of the cell by InputBox [=Var1]
select the character to use for padding by inputbox [=Var2]
left pad the cells according to the above input.

Any help very much appreciated.

Grtz
A

[Roel Jongman]

Have a look if the code below gives you what you need

Sub CellPadding()

Length = InputBox(Prompt:="Enter number of repeats", Title:="Cell Padding - Repetitions")
Charac = InputBox(Prompt:="Enter character", Title:="Cell Padding - Choose character")

If IsNumeric(Length) Then
    For i = 1 To Length
       Cellpad = Cellpad & Charac
    Next i
End If
With Selection
    .Value = Cellpad
    .NumberFormat = "@"
    .HorizontalAlignment = xlLeft
End With
End Sub

Just to point out in case you dont know...
You also have several options without VBA please read this link to see how you can fill out a cell with a function or with (custom) cell formatting
https://www.extendoffice.com/documen...r-in-cell.html

[acolbrantNMBS]

Hi Roel

Thanks for your answer and code.
My question was not so clear, sorry.

Var1 = is the desired length of the string in the cells, not the number of repeats

eg.
Var1 = 9
Var2 = a

Then eg.
12526 ==> aaa12526
526 ==> aaaaaaa526
12526000 ==> a12526000
125265626 ==> 125265626

Thx for your effort/feedback.
i kind of like macroes, since they do the job quite quickly...

Grtz
A

[nigelog]

Try

Sub CellPadding()
Dim Length
Dim Charac As String: Dim cellpad As String: Dim Content As String
Dim Cl As Integer: Dim i As Integer: Dim mylength As Integer
Content = Selection.Text
Cl = Len(Content)
Length = InputBox(Prompt:="Enter Padded Length", Title:="")
Charac = InputBox(Prompt:="Enter character", Title:="")
mylength = Length - Cl
  
    For i = 1 To mylength
       cellpad = cellpad & Charac
    Next i
MsgBox cellpad & Content
Selection = cellpad & Content
End Sub

[acolbrantNMBS]

Hi Nigelog
Thx for your feedback.
That does the trick when one cell is selected; when a range selected marco halts at
Content = Selection.Text

[jindon]

Try

Sub test()
    Dim e
    For Each e In Array(12526, 526, 12526000, 125265626)
        MsgBox LeftPadding(e, 9, "a")
    Next
End Sub

Function LeftPadding(ByVal txt As String, Var1 As Long, Var2 As String) As String
    LeftPadding = IIf(Len(txt) < Var1, String(Var1 - Len(txt), Var2), "") & txt
End Function

[Roel Jongman]

Ok, lets try with this code then..

Sub CellPadding()

Length = InputBox(Prompt:="Enter length of cell", Title:="Cell Padding - Desired Length")
Charac = InputBox(Prompt:="Enter character", Title:="Cell Padding - Choose character")


If IsNumeric(Length) Then
    For Each cl In Selection
        Cellpad = Length - Len(cl.Value)
         If Cellpad > 0 Then
            With cl
                .Value = WorksheetFunction.Rept(Charac, Cellpad) & cl.Value
                .NumberFormat = "@"
                .HorizontalAlignment = xlLeft
            End With
        End If
    Next
End If
End Sub

[acolbrantNMBS]

Hi Roel

That does the trick!
Thx very much, you made my day.

Thx for the other suggestions; quite learned from it as well

Grtz
A