Array CSE Formula in Macro

Cosmos75

Active Member
Joined
Feb 28, 2002
Messages
359
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?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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?
 
Upvote 0
You could consider Dynamic Ranges
-rG
-rI

Then array enter the formula

=MAX(IF(rG="Y",rI))
 
Upvote 0
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
 
Upvote 0
You may want to research redimensioning the
array after it is defined.

Could you define the range and then put it
into the array.
 
Upvote 0
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 & "))"
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?!
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top