+ Reply to Thread
Results 1 to 3 of 3

Ctrl+Shift+Enter (CSE) array formulas in VBA using differing formulas

  1. #1
    Registered User
    Join Date
    07-05-2012
    Location
    here
    MS-Off Ver
    Excel 2003
    Posts
    3

    Ctrl+Shift+Enter (CSE) array formulas in VBA using differing formulas

    Hi I am trying to set a macro using VBA so when I close excel there is a certain range of cells that I need to make an array with but they all have differing formulas
    ex c1 could have =sum(a1:a3) + 5
    while b1 could have =sum(a1:a3)*2

    Is there a way to use the formula array to pick up just the beginning of the formula so just =sum(a1:a3) then have it array the rest based on the length?

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Range("B1:D1").FormulaArray = THIS IS THE PART WHERE I DONT KNOW WHAT TO PUT"
    End Sub
    Thank you

  2. #2
    Registered User
    Join Date
    07-05-2012
    Location
    here
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Ctrl+Shift+Enter (CSE) array formulas in VBA using differing formulas

    Update so I want to use a like statement or contains statement to the right of the = i've been fooling around with InStr(1, cell, "=sum", 1) but still nothing i have found that works
    PHP Code: 
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim cell As Range
    For Each cell In Selection
    InStr(1cell"=sum"1)
    If 
    n Then Range("B1:D1").FormulaArray n
    End Sub 

  3. #3
    Registered User
    Join Date
    07-05-2012
    Location
    here
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Ctrl+Shift+Enter (CSE) array formulas in VBA using differing formulas

    just fyi because i dont think i mentioned it. all the formulas are of the same length

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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