Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Really dumb String Question

  1. #1
    Guest

    Default

    Hi guys,
    Err.. This is kinda stupid, but I am stuck nontheless

    I want to write a formula using VBA that writes formula "=DCOUNTA(database, "Code", criteria)"

    So I wrote something like

    Range("A1").FormulaR1C1 = "=DCOUNTA(database," & ""Code"" & ", criteria)" and it wont run.

    Any help guys? Am getting dumber by the minute

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-07 17:56, Anonymous wrote:
    Hi guys,
    Err.. This is kinda stupid, but I am stuck nontheless

    I want to write a formula using VBA that writes formula "=DCOUNTA(database, "Code", criteria)"

    So I wrote something like

    Range("A1").FormulaR1C1 = "=DCOUNTA(database," & ""Code"" & ", criteria)" and it wont run.

    Any help guys? Am getting dumber by the minute
    Are "database" and "criteria" variables you are using in your macro?

    If not, you can use:

    Range("A1").FormulaR1C1 = "=DCOUNTA(database, ""Code"", criteria)"

    If they are use:

    Range("A1").FormulaR1C1 = "=DCOUNTA(" & database & ",""Code""," & criteria & ")"

    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-07 17:56, Anonymous wrote:
    Hi guys,
    Err.. This is kinda stupid, but I am stuck nontheless

    I want to write a formula using VBA that writes formula "=DCOUNTA(database, "Code", criteria)"

    So I wrote something like

    Range("A1").FormulaR1C1 = "=DCOUNTA(database," & ""Code"" & ", criteria)" and it wont run.

    Any help guys? Am getting dumber by the minute
    Try
    Range("A1").Formula = "=DCOUNTA(database," & """Code""" & ", criteria)"


    Ivan

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You're not being stupid.

    You do know what your problem is though? You're trying to put " into a string, but that's what is used in VBA to denote a string. So you're going to have to use Chr(34) to put double quotes in your formula.

    e.g.

    Chr(34) & "Mark" & Chr(34)

    would return

    "Mark"




    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-03-07 18:08 ]

  5. #5
    Guest

    Default

    Thanks for the replies

    Tried it, but I got

    range = "C5:D6" ' String, and the range is fixed

    =DCOUNTA(database,"Code", 'B5':$F:$F)

  6. #6
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-07 18:23, Anonymous wrote:
    Thanks for the replies

    Tried it, but I got

    range = "C5:D6" ' String, and the range is fixed

    =DCOUNTA(database,"Code", 'B5':$F:$F)
    I don't understand what you mean by your statement above. Can you clarify it for me?


    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  7. #7
    Guest

    Default

    Help guys..

    My code

    Dim db As String, criteria As String, xx As String
    db = "DATABASE" & SVCode
    criteria = Range("B7:C8").Address

    xx = "=DCOUNTA(" & db & ",""Code""," & criteria & ")"

    MsgBox xx

    Range("D6").FormulaR1C1 = xx

    The MsgBox displays the correct formula, range D6 is blank! Anyone??

  8. #8
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-07 18:49, Anonymous wrote:
    Help guys..

    My code

    Dim db As String, criteria As String, xx As String
    db = "DATABASE" & SVCode
    criteria = Range("B7:C8").Address

    xx = "=DCOUNTA(" & db & ",""Code""," & criteria & ")"

    MsgBox xx

    Range("D6").FormulaR1C1 = xx

    The MsgBox displays the correct formula, range D6 is blank! Anyone??
    The formula I gave is basically the same
    ie change Formula.R1c1 to
    Range("D6").Formula = xx

    Ivan

  9. #9
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-07 18:49, Anonymous wrote:
    Help guys..

    My code

    Dim db As String, criteria As String, xx As String
    db = "DATABASE" & SVCode
    criteria = Range("B7:C8").Address

    xx = "=DCOUNTA(" & db & ",""Code""," & criteria & ")"

    MsgBox xx

    Range("D6").FormulaR1C1 = xx

    The MsgBox displays the correct formula, range D6 is blank! Anyone??
    Change
    criteria = Range("B7:C8").Address

    to
    criteria = Range("B7:C8").Address(False, False)

    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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