Topic: Add a space in front of every text on a spreadsheet that is to be link into Auto

[diego65]

Hello,
What we are trying to achieve is to be able to create a macro or a LISP that will allow us to add a blank space infront of every text inside every cell on a spreadsheet by selecting a specific Column.
This spreadsheet will have all drawing numbers listed and Bill of Materails, then linked onto Autocad via Link Manager.
It is posible to manipulate the code below to achieve this?

Sub Macro()
'
' Addspace
'
' Keyboard Shortcut: Ctrl+s
'
    ActiveCell.Columns("A:A").EntireColumn.Select
   
    'cell.Value = cell.Value & "  "

ActiveColumn.Value = ActiveColumn.Value & "  "
     
     
End Sub


Thank you...

[Bhull1985]

Wrong forum, think this should go into the .net one

[MickD]

vba actually

but, try: (not tested! but will get you started)

Code - vb.net: [Select]

1. Sub AddSpaceToTextInCell()
2. Dim r As Range, cell As Range
3. Dim colNumber As Long, colLetter As String
4.  
5. ' get the letter of the active cell's column:
6. colNumber = ActiveCell.Column
7.  
8. 'get the ascii number and turn it to a char, needs
9. 'more work for more tha A-Z columns :)
10. colLetter = Chr(colNumber + 64)
11.  
12. ' create a range to loop through:
13. Set r = Range(colLetter & "1:" & colLetter & "1000")
14.  
15. ' loop through it and add some space to the front of every cell that contains text
16. For Each cell In r
17.     If cell.Text <> "" Then
18.         cell.Text = " " & cell.Text
19.     End If
20. Next
21.  
22. End Sub