TheSwamp

Code Red => .NET => Topic started by: diego65 on January 21, 2014, 04:30:13 PM

Title: Add a space in front of every text on a spreadsheet that is to be link into Auto
Post by: diego65 on January 21, 2014, 04:30:13 PM

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...

Title: Re: Add a space in front of every text on a spreadsheet that is to be link into Auto
Post by: Bhull1985 on January 21, 2014, 05:26:13 PM

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

Title: Re: Add a space in front of every text on a spreadsheet that is to be link into Auto
Post by: MickD on January 22, 2014, 12:54:22 AM

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