=\\\$I\\\ VBA Code Runtime Error
Results 1 to 5 of 5

Thread: VBA Code Runtime Error

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code Runtime Error

    I'm using the following formula in a cell
    =IFERROR(INDEX($A$1:$A$33,AGGREGATE(15,6,(ROW($A$1:$A$33)/(($B$1:$B$33>=$I$1)*($B$1:$B$31<$J$1))),ROWS($A$1:$A2)-ROW($A$1))),"")
    It works perfectly.

    If i enter the same formula in my vba code, I get run time error. How to correct it. In vba code i wrote

    Worksheets("Sheet2").Range("T10").Formula = "=IFERROR(INDEX.$A$1:$A$33,AGGREGATE(15,6,(ROW($A$1:$A$33)/(($B$1:$B$33>=$I$1)*($B$1:$B$31<$J$1))),ROWS($A$1:$A2)-ROW($A$1))),"")"

    What more I have to add, modify?
    Also what shall I do to run the same formula with increments throughout T10-T40



    Regards

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,216
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA Code Runtime Error

    How about
    Code:
    Worksheets("Sheet2").Range("T10:T40").Formula = "=IFERROR(INDEX($A$1:$A$33,AGGREGATE(15,6,(ROW($A$1:$A$33)/(($B$1:$B$33>=$I$1)*($B$1:$B$31<$J$1))),ROWS($A$1:$A2)-ROW($A$1))),"""")"
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    7,761
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Code Runtime Error

    Try: Worksheets("Sheet2").Range("T10").Formula = "=IFERROR(INDEX.$A$1:$A$33,AGGREGATE(15,6,(ROW($A$1:$A$33)/(($B$1:$B$33>=$I$1)*($B$1:$B$31<$J$1))),ROWS($A$1:$A2)-ROW($A$1))),"""")"
    Neil

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code Runtime Error

    Thank you so much. It's working.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,216
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA Code Runtime Error

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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