Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Chapel Hill, North Carolina, USA
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Converting functions to array functions in VBA (2003)

    I have a series of cells beginning with B19 that contain the following functions: =SUM(IF(DeptA_1=1,1,0)) that are supposed to be array functions. (they were array functions and when edited by my staff are no longer).

    I would like to use VBA to change them individually to array functions and tried the following code (with lots of variations) to no avail:

    Sub CreateArrayFunction()
    '
    ' CreateArrayFunction Macro
    ' Macro recorded 10/12/2008 by LMandel
    '

    For iRow = 1 To 1
    For iCol = 1 To 7
    Range("A18").Offset(iRow, iCol).Select
    ' Edit cell
    SendKeys "{F2}"
    ' change to array function
    SendKeys "+^{Enter}" ' control-shift-enter
    Next iCol
    Next iRow

    End Sub

    The code only converts the last cell in the block to an array function. I've added breakpoints, etc to verify that every cell is accessed, but no luck.

    Any idea what I can do to get this to work - there are dozens of worksheets with dozens of cells to fix.

    Thanks!

    Larry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Converting functions to array functions in VBA (2003)

    Try this much shorter version:

    Sub CreateArrayFunction()
    Dim oCell As Range
    For Each oCell In Range("B19:H19")
    oCell.FormulaArray = oCell.Formula
    Next oCell
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Chapel Hill, North Carolina, USA
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting functions to array functions in VBA (2003)

    Hans,

    You are THE MAN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    Thanks.

    Larry

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Converting functions to array functions in VBA (2003)

    Is there a reason why you are not simply using COUNTIF?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Chapel Hill, North Carolina, USA
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting functions to array functions in VBA (2003)

    Good idea. Long story, but we have downloaded 300-600 survey results for 10 different departments, sorted by type of employee. I would have preferred Access, but the person who created this used array functions in Excel and I don't want to have to recreate it.

    Thanks again.

    Larry

Posting Permissions

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