Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Location
    Philippines
    Posts
    3

    Unanswered: problem with single quotes used in formatting reports

    Hi all,

    I'm trying to export a table from access into excel. I'm having some problems with the following code:

    With appXLBook.Application
    .Range("E" & endrownum).Select
    .ActiveCell.FormulaR1C1 = "=COUNTIF(E2:E" & endrownum & ",1)"
    end with

    When the report opens up in excel, this is how the cell looks like:
    =COUNTIF('E2':'E47',1)

    I need to lose the single quotes...

    I've also got another countif statement that I need help with:
    .ActiveCell.FormulaR1C1 = "=COUNTIF(H2:H" & endrownum & ",'<>0'" & ")"

    I tried various other forms for the quotes around <>0, but nothing seems to work. I need the cell to look like this in excel:
    =countif(H2:H47,"<>0")
    using endrownum instead of 47, and double-quotes encompassing <>0. How do I get the cell to look like the above?


    thanks!
    redge

  2. #2
    Join Date
    Jan 2002
    Location
    At the Edge of America!
    Posts
    55
    I think your going to need to use the ASCII tags for this.

    for the colon it is: chr(58)

    should read like this

    With appXLBook.Application
    .Range("E" & endrownum).Select
    .ActiveCell.FormulaR1C1 = "=COUNTIF(E2" & chr(58) & "E" & endrownum & ",1)"
    end with

    When all else fales use the debug.print to see what is getting passed to excel.

    Try that and see if it work.

Posting Permissions

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