Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
Any clue why when I copy the code the ' character does not come with it.
And how do I get around it?
| Check out our Excel VBA Resources | ||||
 |
 |
 |
 |
 |
because excel uses the ' character to signify left justification.
If you do paste special "text with no formatting", it should be fine
Problem is there is no paste special in bisual basic.
I am copying vb into excel formating it and pasting it back to vb.
when it gets back all the comments are gone
How are you formatting it? I just copied some VB comments into a sheet and back with a problem, the formatting holds the answer.
this is my code so far
Sub format()
For x = 1 To Sheet1.Range("A65536").End(xlUp).Row + 1
strtemp = Cells(x, 1)
Cells(x, 1).Select
If Left(strtemp, 3) = "for" Or _
LCase(Left(strtemp, 2)) = "if" Or _
LCase(Left(strtemp, 4)) = "with" Or _
LCase(Left(strtemp, 4)) = "fail" Or _
LCase(Left(strtemp, 11)) = "private sub" Or _
LCase(Left(strtemp, 10)) = "public sub" Or _
LCase(Left(strtemp, 16)) = "private function" Or _
LCase(Left(strtemp, 15)) = "public function" Or _
LCase(Left(strtemp, 12)) = "private enum" Or _
LCase(Left(strtemp, 11)) = "public enum" Then
Cells(x, 1) = strSpaces & strtemp
strSpaces = strSpaces & " "
ElseIf LCase(Left(strtemp, 4)) = "next" Or _
LCase(Left(strtemp, 6)) = "end if" Or _
LCase(Left(strtemp, 8)) = "end with" Or _
LCase(Left(strtemp, 7)) = "end sub" Or _
LCase(Left(strtemp, 8)) = "exit sub" Or _
LCase(Left(strtemp, 8)) = "end enum" Then
strSpaces = Left(strSpaces, Len(strSpaces) - 3)
Cells(x, 1) = strSpaces & strtemp
ElseIf LCase(Left(strtemp, 7)) = "end sub" Or _
LCase(Left(strtemp, 12)) = "end function" Then
strSpaces = Left(strSpaces, Len(strSpaces) - 6)
Cells(x, 1) = strSpaces & strtemp
Else
Cells(x, 1) = strSpaces & strtemp
End If
Next
Cells(x - 1, 1) = strSpaces & strtemp
End Sub