Addinf Spaces

[clares]

Hi There

Is there anyone that can give me assistance. I am preparing a large file for importing and I need to make sure that each cell in my range contains precisly 68 characters including spaces. I have this piece of code but can not work out how to get it to add the spaces. I have tried using the replacement:="(Spaces)" to create the required spaces but is not working?

Sub Add_Spaces()
Application.ScreenUpdating = False
Dim SelectedCell As Range
Dim Letters As String
Dim Spaces As String


For Each SelectedCell In Selection
Letters = ActiveCell.Characters.Count
Spaces = 68 - Letters
Selection.Replace What:="", Replacement:="(Spaces)", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
MsgBox Spaces
Next
Application.ScreenUpdating = True
End Sub

As per usual any help would be gratfully received.

Kind regards

Peter

 

[lenze]

Are you adding spaces at the end or beginning of the cell??
lenze

 

[clares]

Hi There

At the end of the cell.

Kind regards

Peter

 

[lenze]

Sub ReplaceSpace()
Dim cl As Range
For Each cl In Selection
    x = 68 - Len(cl)
    cl = cl & Application.WorksheetFunction.Rept(" ", x)
Next cl
End Sub

lenze

 

[clares]

Thank you very much indeed. Works great.

Kind regards

Peter

 

[Scott Huish]

There are a couple of VBA functions that will do that without resorting to a worksheet function.

Sub ReplaceSpace()
Dim cl As Range, x As Long
For Each cl In Selection
    x = 68 - Len(cl)
    cl = cl & Space(x)
Next cl
End Sub

 

[Scott Huish]

Sub replacespace2()
Dim c As String * 68
Dim cl As Range
For Each cl In Selection
    c = cl: cl = c
Next
End Sub

 

[clares]

Hi Hotpepper

I understand the replacespace code but would you be kind enough to explain replacespace2 to me?

Kind regards

Peter

 

[Scott Huish]

Sure,

I've dimmed the variable c as a fixed length string of 68 characters.
I'm going to assign the value of the cell to this string, which will fill out the ununsed characters with space to the right to reach the fixed length of 68 characters. I then reassign this padded value back to the cell.