MrExcel.com - Your One Stop for Excel Tips & Solutions

' character

Posted by David Rainey on June 01, 2001 7:12 AM

I am trying to copy values from excel into visual basic.
Visual basic uses the ' character as a comment.

Any clue why when I copy the code the ' character does not come with it.

And how do I get around it?

Posted by Bob Findlay on June 01, 2001 7:18 AM

because excel uses the ' character to signify left justification.

If you do paste special "text with no formatting", it should be fine

Posted by DAvid on June 01, 2001 7:30 AM

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

Posted by Jerid on June 01, 2001 7:36 AM


How are you formatting it? I just copied some VB comments into a sheet and back with a problem, the formatting holds the answer.

Posted by David Rainey on June 01, 2001 7:38 AM

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

Posted by david on June 01, 2001 8:20 AM

Is it my formatting?

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