Results 1 to 12 of 12

Thread: How can I apply ctrl+Shift+enter to a number of cells in rows 3 to 6 of activesheet

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2019
    Posts
    8

    How can I apply ctrl+Shift+enter to a number of cells in rows 3 to 6 of activesheet

    How can I apply ctrl+Shift+enter to a number of cells in rows 3 to 6 of active worksheet? I am getting the error: Compile Error: Expected array

    Sub ApplyFormulaArrayToRows()
    '
    ' ApplyCSEToEntireRow Macro
    '
    '
    Dim cell As Long
    Dim Rows As Long

    For Each cell In Rows("3:6").Select
    If cell.HasFormula Then
    Selection.FormulaArray
    End If
    Next cell

    End Sub

  2. #2

    Thread Starter
    New Member
    Join Date
    Sep 2019
    Posts
    8

    Re: How can I apply ctrl+Shift+enter to a number of cells in rows 3 to 6 of activeshe

    Edit: How can I apply ctrl+Shift+enter to All Cells with formulas in rows 3 to 6 of active worksheet?

  3. #3
    Sinecure devotee
    Join Date
    Aug 2013
    Location
    Southern Tier NY
    Posts
    6,582

    Re: How can I apply ctrl+Shift+enter to a number of cells in rows 3 to 6 of activeshe

    I don't work with excel macros, but I have to wonder if you shouldn't have a variable named Rows.

    You declare a variable named Rows as a Long type (a single whole number).

    Dim Rows As Long

    Then expect Rows to be some sort of indexable range. Rows("3:6").

    I assume it can't be both.
    "Anyone can do any amount of work, provided it isn't the work he is supposed to be doing at that moment" Robert Benchley, 1930

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: How can I apply ctrl+Shift+enter to a number of cells in rows 3 to 6 of activeshe

    Welcome to VBForums

    Thread moved to the 'Office Development/VBA' forum... note that while it certainly isn't made clear, the "VB Editor" in Office programs is actually VBA rather than VB, so the 'VB.Net' forum is not really apt

  5. #5

    Thread Starter
    New Member
    Join Date
    Sep 2019
    Posts
    8

    Re: How can I apply ctrl+Shift+enter to a number of cells in rows 3 to 6 of activeshe

    Quote Originally Posted by passel View Post
    I don't work with excel macros, but I have to wonder if you shouldn't have a variable named Rows.

    You declare a variable named Rows as a Long type (a single whole number).

    Dim Rows As Long

    Then expect Rows to be some sort of indexable range. Rows("3:6").

    I assume it can't be both.
    Hello, Thanks for your response. So are you saying that I should replace rows with range?

  6. #6
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: How can I apply ctrl+Shift+enter to a number of cells in rows 3 to 6 of activeshe

    Try this:

    Code:
    Sub ApplyFormulaArrayToRows()
        '
        ' ApplyCSEToEntireRow Macro
        '
        '
        Dim Column As Long
        Dim Row As Long
    
       For Row = 3 To 6
            For Column = 1 To 26
                ActiveSheet.Cells(Row, Column).Select
                If Selection.HasFormula Then
                    Selection.FormulaArray
                End If
            Next Column
        Next Row
    
    End Sub

  7. #7

    Thread Starter
    New Member
    Join Date
    Sep 2019
    Posts
    8

    Re: How can I apply ctrl+Shift+enter to a number of cells in rows 3 to 6 of activeshe

    Thanks. It is showing an error in Selection.formulaArray. FormulaArray method of range class failed.

  8. #8

    Thread Starter
    New Member
    Join Date
    Sep 2019
    Posts
    8

    Re: How can I apply ctrl+Shift+enter to a number of cells in rows 3 to 6 of activeshe

    Quote Originally Posted by jdc2000 View Post
    Try this:

    Code:
    Sub ApplyFormulaArrayToRows()
        '
        ' ApplyCSEToEntireRow Macro
        '
        '
        Dim Column As Long
        Dim Row As Long
    
       For Row = 3 To 6
            For Column = 1 To 26
                ActiveSheet.Cells(Row, Column).Select
                If Selection.HasFormula Then
                    Selection.FormulaArray
                End If
            Next Column
        Next Row
    
    End Sub
    Thanks. It is showing an error in Selection.formulaArray. FormulaArray method of range class failed.

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How can I apply ctrl+Shift+enter to a number of cells in rows 3 to 6 of activeshe

    For Each cell In Rows("3:6")
    that is quite a lot of cells 65536, better to limit the columns in the range
    as rows is a property of a worksheet object, it should not be dimensioned as any variable, which would prevent you code from running, you should also specify the worksheet object, rather than relying on the activeheet being as expected
    you should not be using select

    the formula array property of the range contains the formula, regardless of if the cell contains an array formula
    the hasarray property returns true or false depending if the cell contains an array formula, but appears to be readonly
    you need to enter the formula as the formulaarray, so try

    Code:
    for each cel in objws.rows("3:6")
        if cel.hasformula then cel.formulaarray = cel.formula
    next
    where objws is a worksheet object
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    New Member
    Join Date
    Sep 2019
    Posts
    8

    Re: How can I apply ctrl+Shift+enter to a number of cells in rows 3 to 6 of activeshe

    Quote Originally Posted by westconn1 View Post
    that is quite a lot of cells 65536, better to limit the columns in the range
    as rows is a property of a worksheet object, it should not be dimensioned as any variable, which would prevent you code from running, you should also specify the worksheet object, rather than relying on the activeheet being as expected
    you should not be using select

    the formula array property of the range contains the formula, regardless of if the cell contains an array formula
    the hasarray property returns true or false depending if the cell contains an array formula, but appears to be readonly
    you need to enter the formula as the formulaarray, so try

    Code:
    for each cel in objws.rows("3:6")
        if cel.hasformula then cel.formulaarray = cel.formula
    Is there a difference between cell and cel?
    next
    where objws is a worksheet object
    Hi, Thanks so much! I tried this but it is giving an error. Do I Dim something? Objws?

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How can I apply ctrl+Shift+enter to a number of cells in rows 3 to 6 of activeshe

    Do I Dim something? Objws?
    it is always better to dimension variables, and it is always better to specify which objects you are working with, i just used an imaginary worksheet object but you could equally use
    Code:
    for each cel in sheets("somesheetname").rows("3:6")      '  specific sheet object no variable required
    if you accessing a specific worksheet many times within the code or working with multiple workbooks, better to use variables, like
    Code:
    set objshta = workbooks("myworkbookname.xlsx").sheets("sheet1")
    set objshtb = workbooks("book2").sheets("sheet3")
    of course if you use meaningful names for your variables, it makes it much easier when writing the code to know which sheet you are actually working with

    if you only have one workbook open with only one worksheet, you can pretty well rely that you are working with the correct worksheet, but it definitely good practice to fully qualify all ranges, also some properties ie. usedrange for one, actually require the worksheet object to be specifies and will error without
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    New Member
    Join Date
    Sep 2019
    Posts
    8

    Re: How can I apply ctrl+Shift+enter to a number of cells in rows 3 to 6 of activeshe

    Quote Originally Posted by westconn1 View Post
    it is always better to dimension variables, and it is always better to specify which objects you are working with, i just used an imaginary worksheet object but you could equally use
    Code:
    for each cel in sheets("somesheetname").rows("3:6")      '  specific sheet object no variable required
    if you accessing a specific worksheet many times within the code or working with multiple workbooks, better to use variables, like
    Code:
    set objshta = workbooks("myworkbookname.xlsx").sheets("sheet1")
    set objshtb = workbooks("book2").sheets("sheet3")
    of course if you use meaningful names for your variables, it makes it much easier when writing the code to know which sheet you are actually working with

    if you only have one workbook open with only one worksheet, you can pretty well rely that you are working with the correct worksheet, but it definitely good practice to fully qualify all ranges, also some properties ie. usedrange for one, actually require the worksheet object to be specifies and will error without
    Thanks so much!

Posting Permissions

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



Click Here to Expand Forum to Full Width