+ Reply to Thread
Results 1 to 4 of 4

VBA Array Formula

Hybrid View

hobbiton73 VBA Array Formula 03-21-2014, 09:36 AM
JBeaucaire Re: VBA Array Formula 03-21-2014, 09:41 AM
JosephP Re: VBA Array Formula 03-21-2014, 10:00 AM
hobbiton73 Re: VBA Array Formula 03-21-2014, 10:22 AM
  1. #1
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    VBA Array Formula

    Hi, I wonder whether someone may be able to help me please.

    I'm uising the code below to try and apply, amongst other functionality a formula to a given cell, but I'm having difficulty in applying the formula.

    Sub Slide1Format()
        
        Dim LastRow As Long
        
        Application.ScreenUpdating = False
        
        Const StartRow As Long = 5
        Sheets("Slide 1").Select
        
        With Range("B2")
            .Value = "Monthly Actuals Used"
            .HorizontalAlignment = xlCenter
            .Interior.ColorIndex = 11
            With .Font
                .Name = "Lucida Sans"
                .Bold = True
                .Size = 11
                .ColorIndex = 2
            End With
        End With
        
        With Range("B3")
            .Value = Evaluate("EoMonth(Today(), -2) + 1")
            .NumberFormat = "mmm yy"
            .HorizontalAlignment = xlCenter
            .Interior.ColorIndex = 37
            With .Font
                .Name = "Lucida Sans"
                .Bold = True
                .Size = 10
            End With
        End With
      
        With Range("B7").End(xlDown)
            With .Offset(7, 0)
                .Value = "BAS Consultants"
                .HorizontalAlignment = xlCenter
                .Interior.ColorIndex = 11
                With .Font
                    .Name = "Lucida Sans"
                    .Bold = True
                    .Size = 11
                    .ColorIndex = 2
                End With
            End With
            
            With .Offset(2, 0)
                .Value = "Total No. of Projects (C&R Only)"
                .HorizontalAlignment = xlCenter
                .Interior.ColorIndex = 37
                With .Font
                    .Name = "Lucida Sans"
                    .Bold = True
                    .Size = 10
                    .ColorIndex = 1
                End With
            End With
            
            With .Offset(3, 0)
               Selection.FormulaArray = "=SUM(IF(FREQUENCY(IF(IFPRLOB=""Consultancy & Requirements"",IF(LEN(IFPPN)>0,MATCH(IFPPN,IFPPN,0),"")), IF(IFPRLOB=""Consultancy & Requirements"",IF(LEN(IFPPN)>0,MATCH(IFPPN,IFPPN,0),"")))>0,1))"
            End With
             
        End With
        Columns("B:L").AutoFit
    End Sub
    This is the line of code I'm having difficulty which I've put togther from posts I've read on this and other forums:

    With .Offset(3, 0)
               Selection.FormulaArray = "=SUM(IF(FREQUENCY(IF(IFPRLOB=""Consultancy & Requirements"",IF(LEN(IFPPN)>0,MATCH(IFPPN,IFPPN,0),"")), IF(IFPRLOB=""Consultancy & Requirements"",IF(LEN(IFPPN)>0,MATCH(IFPPN,IFPPN,0),"")))>0,1))"
            End With
    Manually entered, the formula works fine, but when I run this I receive the following error:

    Run time error '1004' Unable to set the FormulaArray property of the Range Class
    I just wondered whether someone could look at this please and let me know where I've gone wrong.

    Many thanks and kind regards

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA Array Formula

    Try just removing the word Selection, leave that period after it...
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: VBA Array Formula

    you've gotta double all the quotes too

    .FormulaArray = "=SUM(IF(FREQUENCY(IF(IFPRLOB=""Consultancy & Requirements"",IF(LEN(IFPPN)>0,MATCH(IFPPN,IFPPN,0),"""")), IF(IFPRLOB=""Consultancy & Requirements"",IF(LEN(IFPPN)>0,MATCH(IFPPN,IFPPN,0),"""")))>0,1))"
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  4. #4
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Array Formula

    Hi @Joseph, thank you very much for taking the time to reply to my post and for the code revision. In conjunction with the guidance from @JBeaucaire, this now works perfectly.

    All the best and kind regards

+ 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. Array formula + Array formula with criteria that lookups a Table
    By anrichards22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-16-2013, 11:41 AM
  2. Typing an array into a formula rather than referring to an array of cells
    By Cookstein2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-03-2013, 12:59 PM
  3. Replace hard coded array in formula with link to input array
    By David Brown in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-08-2011, 07:45 AM
  4. Converting 3x10 array to a 1X30 array to run a Match formula
    By NBVC in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-08-2009, 07:45 AM
  5. Replies: 0
    Last Post: 07-27-2005, 11:05 AM

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