Array CSE Formula in Macro

[Cosmos75]

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?

[BabyTiger]

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?

[Dave Patton]

You could consider Dynamic Ranges
-rG
-rI

Then array enter the formula

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

[Cosmos75]

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 ]

[Dave Patton]

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

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

[BabyTiger]

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 & "))"

[BabyTiger]

My apologies, the second line should be:


rowcounter = Selection.SpecialCells(xlLastCell).Row

[Cosmos75]

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 ]

[BabyTiger]

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.

[Cosmos75]

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?!