Quotes at the begining and ending of each cell

[ng6971]

Hi All,

Below is the list in a worksheet:

<TABLE style="WIDTH: 276pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=368 x:str><COLGROUP><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4064" width=127><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4192" width=131><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 3520" width=110><TBODY><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=14 width=127 x:num="101">Col. A
0101


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26 width=131 x:fmla='=TEXT(A1,"0000")'>Col. B
010100


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 width=110>Col. C
\Chapters\Chap01.txt


</TD></TR></TBODY></TABLE>

Results wanted:

<TABLE style="WIDTH: 276pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=368 x:str><TBODY><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=14 width=127 x:num="101">Col. A
"0101",


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26 width=131 x:fmla='=TEXT(A1,"0000")'>Col. B
"010100",


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 width=110>Col. C
"\Chapters\Chap01.txt"


</TD></TR></TBODY></TABLE>

How can I do this? Thanks in advance.

 

[drgs]

For each Cell in Selection
    Cell.Formula = Chr(34) & Cell.Formula & Chr(34)
Next Cell

 

[VoG]

Try running this macro

Sub AddQ()
Dim c As Range
For Each c In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
    c.Value = Chr(34) & c.Value & Chr(34)
Next c
End Sub

 

[ng6971]

Thanks for quick response.

Code works. After the code execute:

"xxxxx" [Col. A]
"xxxxx" [Col. B]
"xxxxx" [Col. C]

But as you see in my question I want to add comma in Col. A and Col. B:

"xxxxx", [Col. A]
"xxxxx", [Col. B]
"xxxxx" [Col. C] comma is not added in this column.

Once again thanks both of you.

 

[VoG]

Whose code did you run?

Are you saying that you want "" added in columns A:C and the code only worked on A:B or that you have columns A:C but you only want " in columns A:B?

 

[ng6971]

Hi VoG,

I run your code and its works perfectly. Code executes in all the columns which have data in worksheet. Thats I needed.

After code runs:
"xxxxx"
"xxxxx"

But I want to added comma also [,] after "xxxx" only in Col. A & Col. B and not in other columns in the following manner:
"xxxx",
"xxxx",

Thanks

 

[VoG]

Try

Sub AddQ()
Dim c As Range
For Each c In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
    c.Value = Chr(34) & c.Value & Chr(34)
Next c
For Each c In Columns("A:B").SpecialCells(xlCellTypeConstants)
    c.Value = c.Value & ","
Next c
End Sub

 

[drgs]

Are you trying to convert your file to csv?

    With ActiveSheet
        For Each Cell In .UsedRange.Cells
            Cell.Formula = Chr(34) & Cell.Formula & Chr(34) & IIf(Cell.Column < .UsedRange.Column + .UsedRange.Columns.Count - 1, ",", "")
        Next Cell
    End With

 

[ng6971]

Thanks VoG.