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

    Formula in Excel via VBA (Access 2003 SP 3)

    I have tried the following two lines of code individually with the same results for both. I get "1004 Application-defined or object-defined error" when running it. I am simplying trying to compare to the values in two cells.
    .Range("I5").Formula = "=IF(G5=C5,'ok','No Match')"
    .Range("I5:I173").Formula = "=IF(G5=C5," & Chr(34) & "ok" & Chr(34) & "," & Chr(34) & "No Match" & Chr(34) & "')"""

    Below is the code in context, if that helps:
    with xlapp
    .Range("E3").Select
    .Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
    .Columns("E:G").Select
    .Columns("E:G").EntireColumn.AutoFit
    .Range("I5").Select

    .Application.CutCopyMode = False
    ' .Range("I5:I173").Formula = "=IF(G5=C5,'ok','No Match')"
    .Range("I5:I173").Formula = "=IF(G5=C5," & Chr(34) & "ok" & Chr(34) & "," & Chr(34) & "No Match" & Chr(34) & "')"""
    .Range("I2").Formula = "1st Pass Check 4 New Profit Center"

    End With

    Can you tell me what I am missing?

    Thanks in advance for your ideas,

    Ken

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

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

    Re: Formula in Excel via VBA (Access 2003 SP 3)

    You've got several superfluous single and double quotes in the formula. Try this:
    <code>
    .Range("I5:I173").Formula = "=IF(G5=C5,""OK"",""No Match"")"
    </code>
    The best way to debug code like this is to split it up and to display intermediate results:
    <code>
    Dim strFormula As String
    strFormula = "=IF(G5=C5,""OK"",""No Match"")"
    MsgBox strFormula
    .Range("I5:I173").Formula = strFormula
    </code>
    You'll be able to visually inspect the formula the code is trying to create.

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

    Re: Formula in Excel via VBA (Access 2003 SP 3)

    Hans,
    THANKS, both for the fix and the debug tool.

    I am not sure why the ampersand was not required. Typically I thought you had to join strings with ampersands. Is that not required in Excel?

    Thanks again.

    Ken

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

    Re: Formula in Excel via VBA (Access 2003 SP 3)

    It works the same in all applications.

    One way to include double quotes in a quoted string is to double them. You can also concatenate with Chr(34). Both work equally well, so which one you use is a matter of personal preference. I admit to being inconsistent: in Access I mostly use concatenation with Chr(34) because the expressions are generally more complicated, while in Excel I mostly use double double quotes. <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

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

    Re: Formula in Excel via VBA (Access 2003 SP 3)

    Thanks Hans. I was trying to use the concatenation approach but had too many, which I now see.

Posting Permissions

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