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. #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.

  3. #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

  4. #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>

  5. #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
  •