Array CSE Formula in Macro
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Array CSE Formula in Macro

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to add an Array Formula in my macro but it isnít working. Here's the formula:

    {=MAX(IF(G2:G207="Y",I2:I207))}

    I need the ranges for the G and I Column to be done with using the length of Column I minus the Heading so it Column I has 35 values, I need the ranges to be G2:G36 and I2:I36.

    Range("I2").Select
    Range(Selection, Range("I65536").End(xlUp)).Select
    RowCounter = Selection.Count
    Range("L7").Select
    Selection.FormulaArray = "=MAX(IF(G2:" & Range("G" & RowCounter + 1).Address & "=Y,(I2:" & Range("I65536").End(xlUp).Address & "))"

    Can anybody help?

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    The formula you are trying to use doesn't work in Excel. {=MAX(IF(G2:G207="Y",I2:I207))}

    Just wondering what are you trying to achieve?


  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,334
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    You could consider Dynamic Ranges
    -rG
    -rI

    Then array enter the formula

    =MAX(IF(rG="Y",rI))

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    BabyTiger,

    {=MAX(IF(G2:G207="Y",I2:I207))}

    The fomula does work, (I know I use it)but you have to type

    =MAX(IF(G2:G207="Y",I2:I207)) and then enter it by using Crtl-Shift-Enter and not Enter by itself.

    Dave Patton,

    Do Dynamic Ranges work in a Macro?


    Beside shouldn't the using Range("I2",Range("I365536").End(xlUp).Address).Select accomplish the same thing in a macro?

    What I am trying to do is to get excel enter the formula and the copy it to a range that is based on the length of the list it refers to. I can get the ranges to work but am not quite sure why the array formula entered in the macro doesn't work.

    [ This Message was edited by: Cosmos75 on 2002-03-21 12:58 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,334
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    You may want to research redimensioning the
    array after it is defined.

    Could you define the range and then put it
    into the array.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, wasn't thinking.

    You might want to try the following:

    Range("I2").Select
    rowcounter = Selection.Cells(xlLast).Row
    Range("L7").Select
    Selection.FormulaArray = "=MAX(IF(G2:G" & rowcounter & "=" & """" & "Y" & """" & ",I2:I" & rowcounter & "))"


  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My apologies, the second line should be:


    rowcounter = Selection.SpecialCells(xlLastCell).Row


  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    BabyTiger,
    THANKS! It works great! Guess I didn't have enough "s, actually not sure when and where to put them

    Range("I2").Select
    rowcounter = Selection.Cells(xlLast).Row
    Range("L7").Select
    Selection.FormulaArray = "=MAX(IF(G2:G" & rowcounter & "=" & """" & "Y" & """" & ",I2:I" & rowcounter & "))"

    What's the difference between
    rowcounter = Selection.SpecialCells(xlLastCell).Row and
    rowcounter = Selection.Cells(xlLast).Row ?

    I used the first one and it worked? Haven't tried the second one yet.

    THANKS A BUNCH!!

    Edit: I just noticed that the first time it only choose I2:I2 and the second one corrected it.

    It does hoever choose I2:I1003, and there's nothing below I71??

    Also when I do an Range("D65336").End(xlup).Select, it chooses D501 and there's nothing there as well. D2:D63 has a list of A,B,Cs with some empty cells. From D501 if I do an End(xlUp) it goes straight to D1? Any ideas why?

    [ This Message was edited by: Cosmos75 on 2002-03-21 14:23 ]

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    The first line of code:
    selection.cells(xllast).row is incorrect, basically, it didn't go anywhere.

    The second line I have suggested, is selecting the row number of the last cell of the active sheet. Same thing as when you press "ctrl" + "end".

    So that will cover all the activecells you have got on the page.

    Sorry for the lack of explanation.

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ††
    BabyTiger,

    Thanks for the explanation! I've been tring to pick up some VBA Language by messing around with macros provided by people with you, usually without an explanation so the fact that you were willing to explain that to me is of great help!

    THANKS

    When I do Ctrl-End it brings me to Q1003, I have no data below row 71 and beyond Column Q. Have no idea what is going on?!

User Tag List

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
  •  

 

DMCA.com