Macro VBA: CellLength Setting

[lsteffen]

Good afternoon,

I am trying to get a macro to format a cell to be 14 characters in length regardless of the size of the data in the field.

Example:

Before Macro Runs:
B1 = [650]

After Macro Runs:
B1=[ 650]

My current code, both ways, makes the cell 14 characters plus the character and in the above example, B1 has 17 characters. Here are my two versions of code. Can someone please help? Thank you in advance for your help!!! Liz

Version 1

Dim LastRow As Integer
LastRow = ActiveSheet.UsedRange.Rows.Count
Columns("B:B").Select
Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("G1").Formula = "=RIGHT(RC[-5]& REPT("" "",14),14)"
Worksheets("Data").Range("G1:G" & LastRow).FillDown
Columns("G:G").Select
Selection.Copy
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Columns("B:B").AutoFit
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Range("B1").Select

Version 2

'Dim NumSpaces As Long
'Dim LastRow As Integer
'LastRow = ActiveSheet.UsedRange.Rows.Count
'NumSpaces = 14
'Columns("G:G").Select
'Selection.NumberFormat = "General"
'Range("G1").Formula = "=REPT("" ""," & NumSpaces & ")&RC[-5]"
'Worksheets("Data").Range("G1:G" & LastRow).FillDown
'Columns("G:G").Select
'Selection.Copy
'Columns("B:B").Select
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Columns("B:B").AutoFit
'Columns("G:G").Select
'Selection.Delete Shift:=xlToLeft
'Columns("B:B").Select
'Selection.NumberFormat = "@"
'Range("B1").Select

 

[Scott Huish]

First off, there is rarely any need to select Cells in order to work with them. You can work with ranges directly in VBA.

Sub test()
Dim s As String * 14, c As Range
For Each c In Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
    c.NumberFormat = "@"
    s = Right(c, 14)
    c = s
Next
End Sub

 

[shg]

Another way:

Sub x()
    With Range("B1", Cells(Rows.Count, "B").End(xlUp))
        .NumberFormat = "@"
        .Value = Evaluate("if(row(), right(""             "" & " & .Address & ",14))")
    End With
End Sub

 

[lsteffen]

Both answers produce the desired results. Thank you so much!!

Liz