Wrapping cell contents in quotation marks

  • Thread starter Thread starter Daminc
  • Start date Start date
D

Daminc

Baring in mind I'm still a novice at this I thought I'd post this macr
I've just built in case it might help somebody.

This forum has helped me lots of times and I'd like to do my part :))


Code
-------------------
Public Sub quotes()

Dim lastrow As Long, i As Long
Dim first As String, second As String

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow

first = Cells(i, "C")
second = Chr(34) & first & Chr(34)
Cells(i, "C") = second
Next i
End Su
-------------------


The code Chr(34) represents quotation marks

If you want to change the wrapping symbols you just have to change th
number

i.e.
Chr(91) = [
Chr(93) = ]

There may be an easier way to do this but I don't know it yet
 
Daminic,

It's nice to see somebody posting something that somebody might find useful,
instead of just answering questions. Coud you simplify your code thus:

Sub Quotes()

Dim CheckRange As Range
Dim CheckCell As Range
Set CheckRange = ActiveSheet.Range("CheckRange2")

For Each CheckCell In CheckRange
CheckCell.Value = Chr(34) & Chr(34) & CheckCell.Value & Chr(34)
Next

End Sub

You could also replace

For Each CheckCell In CheckRange

with

for each checkcell in selection

so the macro works with whatever cells you have selected as against a
specific range.

Just a thought :-)

Pete
 
"Daminc"

Here's a quick way to produce a list of all the control characters you can
use.

Sub ControlChars()

Dim ControlCount As Integer

Range("J1").Select 'where the list is to start
For ControlCount = 1 To 255
Selection.Value = Chr(ControlCount)
Selection.Offset(0, 1).Value = "CHR(" & ControlCount & ")"
Selection.Offset(1, 0).Select
Next

End Sub

Cheers

Pete
 
Thanks Pete, I'll copy that info into a work doc for future reference
The macro I built was for a specific problem one of my co-worker
wanted sorting out and it works for that but your adaption may b
needed if another problem comes along :
 
Back
Top