Transcribe the max function
Anne-Sophie
-
Iternity Posted messages 43 Status Membre -
Iternity Posted messages 43 Status Membre -
```vba
Bonjour,
Je voudrais savoir comment écrire la fonction Excel MAX en VBA, car je souhaite que dans une cellule s'inscrive le nombre le plus élevé parmi les cellules B9 à L9.
J'ai écrit ActiveCell.Offset(11, 7).Value = Application.WorksheetFunction.Max(Range("B9:L9")) mais lorsque j'exécute ma macro, cela indique "erreur de compilation : sous-programme ou fonction non définie". Et je n'arrive pas à résoudre le problème.
Dernière question : comment faire pour qu'Excel inscrive dans une autre cellule la colonne où figure le plus grand nombre dans la plage de cellules B9 à L9.
Pour être plus clair : les cellules B9 à L9 correspondent à des totaux. Je voudrais qu'Excel inscrive dans une cellule le résultat le plus élevé (de B9 à L9) et qu'ensuite il inscrive dans une autre cellule la colonne correspondant au chiffre le plus grand.
D'avance merci. ```
Je voudrais savoir comment écrire la fonction Excel MAX en VBA, car je souhaite que dans une cellule s'inscrive le nombre le plus élevé parmi les cellules B9 à L9.
J'ai écrit ActiveCell.Offset(11, 7).Value = Application.WorksheetFunction.Max(Range("B9:L9")) mais lorsque j'exécute ma macro, cela indique "erreur de compilation : sous-programme ou fonction non définie". Et je n'arrive pas à résoudre le problème.
Dernière question : comment faire pour qu'Excel inscrive dans une autre cellule la colonne où figure le plus grand nombre dans la plage de cellules B9 à L9.
Pour être plus clair : les cellules B9 à L9 correspondent à des totaux. Je voudrais qu'Excel inscrive dans une cellule le résultat le plus élevé (de B9 à L9) et qu'ensuite il inscrive dans une autre cellule la colonne correspondant au chiffre le plus grand.
D'avance merci. ```
4 réponses
Hello,
Demonstration:
Lupin
Demonstration:
Sub Test_Max_Sum() Dim Cells As Range Dim TheCell As Range Set Cells = Range("B9:L9") Set Cells = ActiveSheet.Range("B9:L9") Range("B11").Value = Application.WorksheetFunction.Sum(Cells) Range("B12").Value = Application.WorksheetFunction.Max(Cells) For Each TheCell In Cells If (TheCell.Value = Range("B12").Value) Then Range("B13").Value = TheCell.Column 'Range("B13").Value = TheCell.Address End If Next Range("A1").Select End Sub Lupin
re:
it was a pleasure, and if you have any further difficulties, feel free
to come back in this thread. The recognition
always inspires me.
Lupin
it was a pleasure, and if you have any further difficulties, feel free
to come back in this thread. The recognition
always inspires me.
Lupin
Hello,
I am a novice myself and I have questions when reading the code
displayed above.
Aren't these lines a repetition?
while, in the problem statement, these cells are part of the range to be examined
by the Max() function.
And finally...
by Anne-Sophie?
I was not familiar with the method “Application.WorksheetFunction.” I just learned
something new that will be very useful to me. Thank you very much.
I don't know if I am violating the forum rules by writing this message which is rather
a sub-question? If so, would it have been better to send a private message to Mr. Lupin?
Pierre Cloutier
I am a novice myself and I have questions when reading the code
displayed above.
Aren't these lines a repetition?
Set Cellules = Range("B9:L9") Set Cellules = ActiveSheet.Range("B9:L9")And here: Range("B11").Value = Application.WorksheetFunction.Sum(Cellules) Range("B12").Value = Application.WorksheetFunction.Max(Cellules)I'm really confused. You are modifying the values of B11, B12 and further B13 while, in the problem statement, these cells are part of the range to be examined
by the Max() function.
And finally...
Range("B12").Value = Application.WorksheetFunction.Max(Cellules)... isn't this line itself a complete answer to the problem stated by Anne-Sophie?
I was not familiar with the method “Application.WorksheetFunction.” I just learned
something new that will be very useful to me. Thank you very much.
I don't know if I am violating the forum rules by writing this message which is rather
a sub-question? If so, would it have been better to send a private message to Mr. Lupin?
Pierre Cloutier
Hello,
Your observation is quite relevant, and you are almost right!
You need to be able to define the range
Set Cells = Range("B9:L9")
is also a minimum although very vague,
and the declaration of the range must correspond to the requested function
which is:
Application.WorksheetFunction.Max
The Member [ WorksheetFunction ] implies a sheet,
so I recommend using the range declaration more explicitly with a sheet:
Set Cells = ActiveSheet.Range("B9:L9")
or
Set Cells = Sheets("MySheet").Range("B9:L9").
A striking example of how to call a function :-)
InputBox
Application.InputBox
You should see a difference :-)
and you are welcome just as much as I am,
for my part, I am a stranger here :-)
when I have time :-)
and sometimes I code these improvisational drafts in a few minutes,
I test them and I probably had to add the specification of the sheet while forgetting to remove the first line.
Lupin
Your observation is quite relevant, and you are almost right!
You need to be able to define the range
Set Cells = Range("B9:L9")
is also a minimum although very vague,
and the declaration of the range must correspond to the requested function
which is:
Application.WorksheetFunction.Max
The Member [ WorksheetFunction ] implies a sheet,
so I recommend using the range declaration more explicitly with a sheet:
Set Cells = ActiveSheet.Range("B9:L9")
or
Set Cells = Sheets("MySheet").Range("B9:L9").
A striking example of how to call a function :-)
InputBox
Application.InputBox
You should see a difference :-)
and you are welcome just as much as I am,
for my part, I am a stranger here :-)
when I have time :-)
and sometimes I code these improvisational drafts in a few minutes,
I test them and I probably had to add the specification of the sheet while forgetting to remove the first line.
Lupin
Hello,
This thread is quite similar to my issue (at least I think so...). I’m going to post here for more coherence.
In my worksheet, I'm using validation rules that involve dynamic ranges. Basically, my users select a country, then a district, then a sub-district, etc., and I want the choices available at each level to be consistent with the choice made at the upper level.
For example, for France, only the French regions should be available (in the validation dropdown list).
In my worksheet, the validation formula is as follows:
=OFFSET(levmomA1,MATCH(D9,Admin1_Ref,0),1,COUNTIF(Admin1_Ref,D9),1)
levmomA1 and Admin1_Ref are ranges, and D9 refers to the value chosen at the upper level.
It’s impossible to transfer this to VBA (or these data must be entered via ComboBoxes). My latest attempt looks like this:
'Define Ranges and their Formulas'
Dim RangeLevmomA1 As Range
Dim RangeAdmin1_Ref As Range
Dim ComboBox_Ad2Range As Range
Set RangeLevmomA1 = ActiveWorkbook.Worksheets("_geo").Range("levmomA1")
Set RangeAdmin1_Ref = ActiveWorkbook.Worksheets("_geo").Range("Admin1_Ref")
Set ComboBox_Ad2Range = ActiveWorkbook.Worksheets("_geo").WorksheetFunction.Offset(RangeLevmomA1, WorksheetFunction.Match(ComboBox_Ad1.Value, RangeAdmin1_Ref, 0), 1, WorksheetFunction.CountIf(RangeAdmin1_Ref, ComboBox_Ad1.Value), 1)
'Define ComboBox_Ad2 RowSource Definition'
ComboBox_Ad2.RowSource = ComboBox_Ad2Range
If you have an idea... you would take a thorn out of my side...
This thread is quite similar to my issue (at least I think so...). I’m going to post here for more coherence.
In my worksheet, I'm using validation rules that involve dynamic ranges. Basically, my users select a country, then a district, then a sub-district, etc., and I want the choices available at each level to be consistent with the choice made at the upper level.
For example, for France, only the French regions should be available (in the validation dropdown list).
In my worksheet, the validation formula is as follows:
=OFFSET(levmomA1,MATCH(D9,Admin1_Ref,0),1,COUNTIF(Admin1_Ref,D9),1)
levmomA1 and Admin1_Ref are ranges, and D9 refers to the value chosen at the upper level.
It’s impossible to transfer this to VBA (or these data must be entered via ComboBoxes). My latest attempt looks like this:
'Define Ranges and their Formulas'
Dim RangeLevmomA1 As Range
Dim RangeAdmin1_Ref As Range
Dim ComboBox_Ad2Range As Range
Set RangeLevmomA1 = ActiveWorkbook.Worksheets("_geo").Range("levmomA1")
Set RangeAdmin1_Ref = ActiveWorkbook.Worksheets("_geo").Range("Admin1_Ref")
Set ComboBox_Ad2Range = ActiveWorkbook.Worksheets("_geo").WorksheetFunction.Offset(RangeLevmomA1, WorksheetFunction.Match(ComboBox_Ad1.Value, RangeAdmin1_Ref, 0), 1, WorksheetFunction.CountIf(RangeAdmin1_Ref, ComboBox_Ad1.Value), 1)
'Define ComboBox_Ad2 RowSource Definition'
ComboBox_Ad2.RowSource = ComboBox_Ad2Range
If you have an idea... you would take a thorn out of my side...
Hello everyone,
Is there a VBA code or a formula that can help me solve this problem?
Article Version Index
IBB102110 0
IBB102110 5
IBB102110 9 OK
IBB102110 9 OK
IBB102194 3
IBB102194 3
IBB102194 3
IBB102194 4 OK
IBB102194 4 OK
IBB102194 4 OK
I want to put in one column an index that indicates that the last version is the highest
I know it's simple with 5 lines but I have 17,000 references to determine their latest versions
Thank you for your help
Is there a VBA code or a formula that can help me solve this problem?
Article Version Index
IBB102110 0
IBB102110 5
IBB102110 9 OK
IBB102110 9 OK
IBB102194 3
IBB102194 3
IBB102194 3
IBB102194 4 OK
IBB102194 4 OK
IBB102194 4 OK
I want to put in one column an index that indicates that the last version is the highest
I know it's simple with 5 lines but I have 17,000 references to determine their latest versions
Thank you for your help
Hello everyone,
I have a column of values where the values go through several mins and several maxs. How can I get them all because when I use, for example, max(A1:A200), I only get one max which is the highest, and the same for min(A1:A200).
Is there a solution with Excel VBA?
I have a column of values where the values go through several mins and several maxs. How can I get them all because when I use, for example, max(A1:A200), I only get one max which is the highest, and the same for min(A1:A200).
Is there a solution with Excel VBA?
Hello,
Without VBA, there is the formula:
=LARGE(range;k) which returns, depending on the k, the largest, the second largest, or the third largest value of your range.
Example:
=LARGE(A1:A100;8) returns the 8th largest value of the range A1:A100
Conversely, you will find the formula =SMALL(A1:A100;2)
Without VBA, there is the formula:
=LARGE(range;k) which returns, depending on the k, the largest, the second largest, or the third largest value of your range.
Example:
=LARGE(A1:A100;8) returns the 8th largest value of the range A1:A100
Conversely, you will find the formula =SMALL(A1:A100;2)
Your help takes a weight off my shoulders, because I have to do this for my job.
Thanks again.