+ Reply to Thread
Results 1 to 8 of 8

Using VBA to Set FormulaArray in cell

  1. #1
    Registered User
    Join Date
    11-02-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    26

    Using VBA to Set FormulaArray in cell

    Hi guys,

    I'm trying to use VBA to replace a formula in a particular cell (eventually this will be triggered by a user selecting a particular country). However, to test things out I've just written some VBA using the FormualArray object which sets the formula already in cell H4 (Sheet: "Grpg Logic Engine") back into it.

    What's strange is that the formula works well when typed into the cell directly. But when you run the macro (which simply sets the exact same formula back into the cell it returns a 'FALSE' value.

    Any insight/help would be much appreciated.

    Cheers,

    Oscar.
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Using VBA to Set FormulaArray in cell

    Double up the quotes.

    Please Login or Register  to view this content.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    11-02-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Using VBA to Set FormulaArray in cell

    Hi TMS,

    thanks for your reply. It is now populating the cell without returning a 'false' value, but it is not running the formula and delivering a result. The formula is just displyed in the cell. Do I have to include the value property in my VBA to get the formula to return a result?

    Cheers,

    Oscar.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Using VBA to Set FormulaArray in cell

    I suspect that the cell you are putting the formula into is formatted as text.

    Regards, TMS

  5. #5
    Registered User
    Join Date
    11-02-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Using VBA to Set FormulaArray in cell

    Hi TMS,

    That's one of the first things I checked before posting on Excel Forum, although I can see why you suggested this as what I described in my last post is a classic symptom of text formatting a formulated cell.

    My thinking is this: If the formula works outside of VBA (typed directly into the cell), but does not work when set by VBA code then there must be something wrong with the code.

    Any other possibilities you can think of?

    Cheers,

    Oscar.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Using VBA to Set FormulaArray in cell

    Nope. Please repost the sample workbook with your current implementation of the code.

    That said, a leading space will stop a formula being evaluated.

    Regards, TMS

  7. #7
    Registered User
    Join Date
    11-02-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Using VBA to Set FormulaArray in cell

    Hi TMS,

    I had the 'Select' object in the code unnecessarily. Once I removed it and placed the 'FormulaArray' property after the range the formula started to work.

    Thank you very much for your help; it made all the difference.

    Best wishes.

    Oscar

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Using VBA to Set FormulaArray in cell

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA FormulaArray
    By jadown in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-19-2013, 11:52 AM
  2. [SOLVED] FormulaArray
    By GreyBates in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-05-2013, 04:56 PM
  3. FormulaArray
    By efernandes67 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-29-2011, 06:32 PM
  4. formulaArray problem
    By dave3n3 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-15-2009, 08:02 AM
  5. Using FormulaArray in VBA
    By marcelobf in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-03-2006, 04:50 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1