Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Sep 2001
    Location
    Philadelphia suburbs
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    excel macro containing quotes (2000,2002,2003)

    I've been trying to write a macro which will surround data in each cell with quotation marks. When I run the macro, even though I am using relative reference, the macro changes the data to what was in the original cell. I've tried going into VBA and playing with the quotation marks, but when I do that I get an error.

    Does anyone know if it is possible to do what I am trying to do?

    Thanks very much.

    Sue

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel macro containing quotes (2000,2002,2003)

    It is kind of hard to guess without seeing the code. Could you post it?
    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: excel macro containing quotes (2000,2002,2003)

    You need a open quote and a close quote. Between the open & close quotes, if you want a quote, you must use two consecutive quotes. So, what you need is
    <pre>Sub QuoteIt()
    ActiveCell = """" & ActiveCell.Text & """"
    End Sub
    </pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Lounger
    Join Date
    Sep 2001
    Location
    Philadelphia suburbs
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel macro containing quotes (2000,2002,2003)

    SammyB,
    Thanks very much for your response. I tried your suggestion for the replacement line, but the first line of code that was generated when I created the macro gave me an error:
    Sub Macro3()
    '
    ' Macro3 Macro
    ' Macro recorded 1/3/2006 by
    '
    ' Keyboard Shortcut: Ctrl+w
    '
    ActiveCell.Offset(-4, 0).Range("A1").Select
    ActiveCell = """" & ActiveCell.Text & """"
    ActiveCell.Offset(1, 0).Range("A1").Select
    End Sub

    When I took out the first line, the quotation mark appeared only after the text. Do you know how I could replace the first line to make it correct?

    Thank you again.

    Sue

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: excel macro containing quotes (2000,2002,2003)

    ActiveCell.Offset(-4, 0).Range("A1").Select
    does not give me an error, but it makes absolutely no sense.

    If, for example, you want to place double quote around cell C4, then the code is
    Range("C4") = """" & Range("C4") & """"
    where there are four double quotes before and after
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Lounger
    Join Date
    Sep 2001
    Location
    Philadelphia suburbs
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel macro containing quotes (2000,2002,2003)

    I think I must be missing something very basic, but something odd is happening. When I run the macro for each cell in the column, the quote marks preceding the text show up in the formula bar, but not in the cell. When I go to the next cell, the quote marks only exist after the text, not before. What I want to do is run the macro for an entire column, placing quote marks before and after the text in each cell. (Actually they are numbers with, usually, preceding 0's. Would the format--text, general, or number have any affect on the macro?)

    I am just an occasional user of Excel, so I think I might be overlooking something that I should be aware of.

    Thanks again,
    Sue

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: excel macro containing quotes (2000,2002,2003)

    I get double quotes on both sides. How is the column formatted? Try the macro in a new workbook & see if it does the same thing.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: excel macro containing quotes (2000,2002,2003)

    Sue

    Let us assume you have a list of words in column 1 with a header in A1 and the list starting in A2.

    You can use this code:

    Sub QuoteIt()
    Dim lstLen As Integer

    lstLen = Range("A65536").End(xlUp).Row

    Range("A2").Select

    For i = 1 To lstLen - 1

    ActiveCell = """" & ActiveCell.Text & """"
    ActiveCell.Offset(1, 0).Select

    Next i
    End Sub

    I have attached a workbook with the code in it and some test data. Run QuoteIt from Tools|Macros
    Jerry

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: excel macro containing quotes (2000,2002,2003)

    The following code will applay quotation marks to all thecells in the selected range.<pre> Sub AddQuotes()
    Dim oCell As Range
    For Each oCell In Selection
    oCell = Chr(34) & oCell.Text & Chr(34)
    Next
    End Sub</pre>

    ActiveCell.Offset(-4, 0).Range("A1").Select will produce an error if the activecell is in any row less than row 5

    Andrew C

  10. #10
    Lounger
    Join Date
    Sep 2001
    Location
    Philadelphia suburbs
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel macro containing quotes (2000,2002,2003)

    Thanks very much to all who replied. I tried all three solutions in both Excel 2000 and Excel 2003. In all cases, they worked correctly in 2003, but in 2000 only the end quotes are generated. The format in both 2000 and 2003 is general. I don't know whether I have a setting of some kind in my Excel 2000 that is causing the problem. I will try it on another machine that is running 2000 and see what happens.

    There is another part to what I want to do that I have a question about. I would like to add code to the macro so that before generating the quotes, leading 0's are added to any text whose length is less than 9, so that the length of the data in each cell is 9. I used to code in Basic, so I'm pretty sure that can be done in vba, but I don't know what the syntax is.

    Again, thank you so much for your helpfullness.

    Sue

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: excel macro containing quotes (2000,2002,2003)

    The text of oCell padded with leading zeros to length 9 is

    Right(String(9, "0") & oCell.Text, 9)

    or if oCell always contains a number:

    Format(oCell, "000000000")

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •