+ Reply to Thread
Results 1 to 3 of 3

Error when using .FormulaArray property of the Range class

  1. #1
    Registered User
    Join Date
    01-02-2020
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    2

    Question Error when using .FormulaArray property of the Range class

    Hi All,

    I just began getting the "Run-time error '1004': Unable to set the FormulaArray property of the range class" in my code.

    Here's the block of code where I'm receiving the error

    Please Login or Register  to view this content.
    The formulas are all pretty much the exact same with some minor tweaks with where they are placing/pulling information, and the code errors on the 3rd line. The first two run fine. The formula also works fine if I manually place it in the correct cell.

    I've uploaded a text file with just the VBA code for this macro, and a scrubbed version of the excel file. The issue I'm having is with the Add_Consultant macro (Module 12) specifically on the 'Summary' sheet.

    I apologize for any mistakes in the files. I inherited this file to add a specific functionality to it, and I think many people have touched it over the years.

    Thanks so much!

    Joe
    Last edited by jlw30; 01-03-2020 at 09:55 AM.

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: Error when using .FormulaArray property of the Range class

    Due to the relative references, your formula tips over the 255 character limit for FormulaArray in J11. A simple fix is to amend the criteria at the start and then replace it, like this:

    Please Login or Register  to view this content.
    Rory

  3. #3
    Registered User
    Join Date
    01-02-2020
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    2

    Re: Error when using .FormulaArray property of the Range class

    Got it. Thanks so much! Did not even think about how the relative references would affect the character limit

+ 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. Replies: 4
    Last Post: 09-16-2019, 07:04 AM
  2. [SOLVED] Run-time error '1004': Unable to set the FormulaArray property of the Range class
    By billj in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-24-2017, 10:30 AM
  3. Unable to set the FormulaArray property of the Range Class
    By bsquad in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-09-2017, 07:28 PM
  4. Unable to set the FormulaArray property of the range class
    By BradleyS in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-22-2013, 07:57 AM
  5. unable to set FormulaArray property of Range Class Error
    By BuglerX in forum Access Programming / VBA / Macros
    Replies: 1
    Last Post: 11-23-2011, 01:13 PM
  6. unable to set FormulaArray property of Range class Error
    By BuglerX in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-23-2011, 12:14 PM
  7. Unable to set the FormulaArray Property of the Range Class - Error 1004
    By manchmal2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-24-2010, 05:08 PM

Tags for this Thread

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