Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Access VBA formula in Excel (A2003 SP 2)

    I know I have this asked and answered before, but I can't for the life of me find it.
    I am using the following VBA code from an Access module to put a formula in a specific cell
    strSQL = "P2:P" & r
    .Range(strSQL).FormulaR1C1 = "=IF(SUM(J2-H2-M2)<U2,(J2-H2-M2),U2)"

    When I open the spreadsheet instead of the above, I have "=IF(SUM('J2'-'H2'-'M2')<'U2',('J2'-'H2'-'M2'),'U2')". For some reason I am getting apostrophe's (') after each cell. What am I doing wrong?

    Thanks in advance for your guidance.

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

    Re: Access VBA formula in Excel (A2003 SP 2)

    FormulaR1C1 expects a formula using R1C1 style cell refererences, not A1 style cell references, so your code is inconsistent. Try

    .Range(strSQL).Formula = "=IF(SUM(J2-H2-M2)<U2,(J2-H2-M2),U2)"

    By the way, it can be simplified to

    .Range(strSQL).Formula = "=MIN(J2-H2-M2,U2)"

    Note: strSQL is a confusing name for a variable that holds a range reference.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Access VBA formula in Excel (A2003 SP 2)

    Hans,

    You are right about the strSQL, but I needed a variable and that one was already diminsioned, so I was lazy and used it. I need to be more methodical on how I use things. I'll give it a try, and I have no doubt it will work. THANKS!

    As a side note, do you know if there was a reason that it automatically placed the apostrophe's in the cell? Or is it just one of those anomalies?

    THANKS!

    Ken

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

    Re: Access VBA formula in Excel (A2003 SP 2)

    The cause of the apostrophes was that the formula was not valid as an R1C1 style formula, so Excel tried to interpret the cell references as names.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Access VBA formula in Excel (A2003 SP 2)

    THANK YOU. It's hard to outsmart these things when they are so logical all the time.

Posting Permissions

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