VBA variable Max function

[TestableEmu263]

Hey everyone, New to VBA and I want to figure out how to create a function that will take the maximum of a range that the user can define via a single cell reference. For example, If I have a list of 10 items and I want the maximum of the first 5 items, I can simply pick that range with the built in max function in excel. But if that list of items is variable and it changes to say 24 items and I instead want to take a max of the first 12 items, how can I go about doing that so that I don't have to manually change the range each time? Thanks for the help

 

[JoeMo]

Maybe:

Book1.xlsm
	A	B	C	D
1	MyRange		numCells	Max
2	2		3	34
3	33		12	37
4	34		18	48
5	26
6	18
7	15
8	1
9	37
10	0
11	34
12	25
13	30
14	20
15	12
16	12
17	28
18	24
19	48
20	2
Sheet4

Cell Formulas
Range	Formula
D2:D4	D2	=MaxOfFirstN($A$2:$A$20,C2)

Function MaxOfFirstN(R As Range, N As Long) As Double
MaxOfFirstN = Application.Max(Range(R.Cells(1, 1), R.Cells(N, 1)))
End Function

 

[TestableEmu263]

Very Close, The only other thing I was trying to make the function do is take the max of the next group as well. So for your example, you posted above, the only change needed would be something that makes the function also able to be carried down into the next respective grouping.

Further info:
1) A list of x entries is input into a column. This list can be any number of items long.
2) this list can then be split into groups. For example, a list of 60 items can be split into 6 groups of 10 or 12 groups of 5.
3) Depending on what the user chooses whether that be the aforementioned 6 or 12 groups, the function then takes the max of each group

Thanks again for your previous reply... and in addition I'm very new to VBA and was wondering if you knew of any resources I could use to learn: Book, youtube channel, etc

 

[JoeMo]

Very Close, The only other thing I was trying to make the function do is take the max of the next group as well. So for your example, you posted above, the only change needed would be something that makes the function also able to be carried down into the next respective grouping.

Further info:
1) A list of x entries is input into a column. This list can be any number of items long.
2) this list can then be split into groups. For example, a list of 60 items can be split into 6 groups of 10 or 12 groups of 5.
3) Depending on what the user chooses whether that be the aforementioned 6 or 12 groups, the function then takes the max of each group

Thanks again for your previous reply... and in addition I'm very new to VBA and was wondering if you knew of any resources I could use to learn: Book, youtube channel, etc

You are welcome - thanks for the reply. I don't see anything in your OP that alludes to a function that returns more than one value. I think a subroutine would be a better choice for the sort of additional information to be returned.