Macro to insert double quotes

[Jimf2]

Hi, I need to export a sheet to CSV format, so I can use that csv file as input to a access table SQL insert statement.
My problem is, I have one column that has data in it with quotes that cannot be altered.
e.g. The "location" field has building, room, cubicle such as "Building 208, Room 19, Cubicle 9"
This causes the building, room and cubicle to be placed in a separate cell. I need them as a single cell in the output. Placing double quotes in that cell before export solves the problem.
I have a macro that will insert the double quotes but there are some rows that are blank in the column.


This is what I've tried:
Line 2 when enabled selects all rows until it reaches a blank cell
Line 3 when enabled selects all rows in the sheet, well below the last line of real data

Sub AddQuotes()
'With Range(Range("G1"), Range("G1").End(xlDown)).Select
With Range("G:G").Select
On Error Resume Next
Dim myCell As Range
     For Each myCell In Selection
             myCell.Value = Chr(34) & myCell.Value & Chr(34)
     Next myCell


End With
End Sub

Here's the problem, using the code as shown works until the column G has a blank cell (at row 10), then the writing of the double quotes terminates before the bottom of the sheet (200 rows).

When I use the 2nd line instead of the third to define the range, I get the whole column double quoted well past the 200 rows of data.

How can I just get the double quotes on the "count of rows" in column G.
Note: if it helps, I can reference another row that has no empty cells for the 200 row count.
Also note the row count figure 200 is really variable, one week it may be 200, the next may be 375, the next may be 225.

 

[Rick Rothstein]

I have a macro that will insert the double quotes but there are some rows that are blank in the column.

What should happen for blank cells... nothing or a pair of quotes only?

 

[kbrummert]

What if you created a macro that applies to everything in a selected range? It would apply to everything in ctrl+shift+end, i.e. the range you are focusing on, regardless of column, blanks, etc....

Sub AddQuotes()

Dim rng As Range

Set rng = Selection
With rng
On Error Resume Next
Dim myCell As Range
     For Each myCell In rng
             myCell.Value = Chr(34) & myCell.Value & Chr(34)
     Next myCell


End With
End Sub

 

[Jimf2]

What should happen for blank cells... nothing or a pair of quotes only?

Hi Rick, thanks for the reply.

Some cells will be blank, but the adjacent cells will have data, so those blank cells will need empty double quotes

e.g

FirstName	LastName	Extension	Address
Fred	Jones	4450	Bldg 20, Rm6, Cubicle8
Sherry	Peters	2391	Bldg 20, Rm6, Cubicle9
Dave	Write	4213	Bldg 21, Rm1, Cubicle2
Dianne	Grey	6697	Bldg 21, Rm2, Cubicle1
Betty	White	4540
Garry	Ball	3578	Bldg 22, Rm4, Cubicle1
William	Stone	4179	Bldg 22, Rm4, Cubicl2
Joyce	Patten	3527	Bldg 22, Rm4, Cubicle5
Terry	McMillan	8573	Bldg 22, Rm4, Cubicle7
Barry	Williams	5567	Bldg 23, Rm2, Cubicle3
Grace	Lee	2314	Bldg 23, Rm2, Cubicle4

<tbody>
</tbody>

Notice Betty White's location is blank, but the macro should input double quotes in that cell.
To me it seems I need to test for the existence of data in a known column (like phone extension) because it will always have data and if data exists there then double quote the blank cell, if the extension is blank AND location is blank then end the macro.

 

[Jimf2]

Hi kbrummert,
Yes, I tried that code you quoted, but it double quotes the rows beyond the bottom of the data. If I only have 200 rows, the double quoted column "G" has double quotes from row 1 to well beyond the last valid row of data. The macro will be execute outside the excel spreadsheet (in powershell) so if there is a way for the macro to do the ctl+shift+end, that is what is needed.

 

[Jimf2]

Pair of quotes for blank cells is necessary until the last row of valid data,

 

[Norie]

Try this.

Sub AddQuotes()

     For Each myCell In Range(Range("G1"), Range("G" & Rows.Count).End(xlUp))
             myCell.Value = Chr(34) & myCell.Value & Chr(34)
     Next myCell

End Sub

 

[Jimf2]

Works Perfect! Thank you.

 

[Rick Rothstein]

You can also use this non-looping macro to produce the same results...

Sub AddQuotes()
  Dim Addr As String
  Addr = Range("G1", Cells(Rows.Count, "G").End(xlUp)).Address(0, 0)
  Range(Addr) = Evaluate("IF({1},""""""""&" & Addr & "&"""""""")")
End Sub

 

[Jimf2]

Thanks Rick,
I'll give that a try too.