| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying the largest number
I am in need of some help regarding the creation of a simple MAX calculation
in my code. The data is something like the following: Col I Col J Col K Col L 12 4 7 19 How would I write a line of code that will identify that within this array Column L has the largest figure? Many thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identifying the largest number
Hello Phil
Msgbox "Max value is in " & _ Evaluate("=ADDRESS(1,MATCH(MAX(I1:L1),I1:L1,0))") HTH Cordially Pascal "Phil" a écrit dans le message de news: ... I am in need of some help regarding the creation of a simple MAX calculation in my code. The data is something like the following: Col I Col J Col K Col L 12 4 7 19 How would I write a line of code that will identify that within this array Column L has the largest figure? Many thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identifying the largest number
A function would do it, assuming your data are in the named range myRange:-
Function MaxAddress(myRange) MaxNum = Application.Max(myRange) For Each Cell In myRange If Cell = MaxNum Then MaxAddress = Cell.Address Exit For End If Next Cell call the funxtion with =maxaddress(myrange) typed in any cell Mike End Function "Phil" wrote: I am in need of some help regarding the creation of a simple MAX calculation in my code. The data is something like the following: Col I Col J Col K Col L 12 4 7 19 How would I write a line of code that will identify that within this array Column L has the largest figure? Many thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identifying the largest number
Ok, this should work better:
MsgBox "Max value is in " & _ Evaluate("=ADDRESS(ROW(A1),MATCH(MAX(1:1),1:1,0))" ) HTH Cordially Pascal "papou" a écrit dans le message de news: ... Hello Phil Msgbox "Max value is in " & _ Evaluate("=ADDRESS(1,MATCH(MAX(I1:L1),I1:L1,0))") HTH Cordially Pascal "Phil" a écrit dans le message de news: ... I am in need of some help regarding the creation of a simple MAX calculation in my code. The data is something like the following: Col I Col J Col K Col L 12 4 7 19 How would I write a line of code that will identify that within this array Column L has the largest figure? Many thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identifying the largest number
Hi Pascal
I think modifying your posting to MsgBox "Max value is in " & _ Evaluate("=ADDRESS(1,MATCH(MAX(I1:L1),I1:L1,0)+col umn(I1)-1)") will resolve the matter -- Regards Roger Govier "papou" wrote in message ... Phil Forget my answer, it will not return the correct address. I will have another look at it. Cordially Pascal "papou" a écrit dans le message de news: ... Hello Phil Msgbox "Max value is in " & _ Evaluate("=ADDRESS(1,MATCH(MAX(I1:L1),I1:L1,0))") HTH Cordially Pascal "Phil" a écrit dans le message de news: ... I am in need of some help regarding the creation of a simple MAX calculation in my code. The data is something like the following: Col I Col J Col K Col L 12 4 7 19 How would I write a line of code that will identify that within this array Column L has the largest figure? Many thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identifying the largest number
Hi Roger
Yes definitely ;-) Cordially Pascal "Roger Govier" a écrit dans le message de news: ... Hi Pascal I think modifying your posting to MsgBox "Max value is in " & _ Evaluate("=ADDRESS(1,MATCH(MAX(I1:L1),I1:L1,0)+col umn(I1)-1)") will resolve the matter -- Regards Roger Govier "papou" wrote in message ... Phil Forget my answer, it will not return the correct address. I will have another look at it. Cordially Pascal "papou" a écrit dans le message de news: ... Hello Phil Msgbox "Max value is in " & _ Evaluate("=ADDRESS(1,MATCH(MAX(I1:L1),I1:L1,0))") HTH Cordially Pascal "Phil" a écrit dans le message de news: ... I am in need of some help regarding the creation of a simple MAX calculation in my code. The data is something like the following: Col I Col J Col K Col L 12 4 7 19 How would I write a line of code that will identify that within this array Column L has the largest figure? Many thanks |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identifying the largest number
Hi Pascal
Wouldn't that return the address of the highest value in row 1, even it were outside the range that the OP wanted? -- Regards Roger Govier "papou" wrote in message ... Ok, this should work better: MsgBox "Max value is in " & _ Evaluate("=ADDRESS(ROW(A1),MATCH(MAX(1:1),1:1,0))" ) HTH Cordially Pascal "papou" a écrit dans le message de news: ... Hello Phil Msgbox "Max value is in " & _ Evaluate("=ADDRESS(1,MATCH(MAX(I1:L1),I1:L1,0))") HTH Cordially Pascal "Phil" a écrit dans le message de news: ... I am in need of some help regarding the creation of a simple MAX calculation in my code. The data is something like the following: Col I Col J Col K Col L 12 4 7 19 How would I write a line of code that will identify that within this array Column L has the largest figure? Many thanks |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identifying the largest number
Yes Roger it would.
Since our friend did not specify (although it seems from his original post that he is looking in a row), he may need to amend to his needs. Cordially Pascal "Roger Govier" a écrit dans le message de news: ... Hi Pascal Wouldn't that return the address of the highest value in row 1, even it were outside the range that the OP wanted? -- Regards Roger Govier "papou" wrote in message ... Ok, this should work better: MsgBox "Max value is in " & _ Evaluate("=ADDRESS(ROW(A1),MATCH(MAX(1:1),1:1,0))" ) HTH Cordially Pascal "papou" a écrit dans le message de news: ... Hello Phil Msgbox "Max value is in " & _ Evaluate("=ADDRESS(1,MATCH(MAX(I1:L1),I1:L1,0))") HTH Cordially Pascal "Phil" a écrit dans le message de news: ... I am in need of some help regarding the creation of a simple MAX calculation in my code. The data is something like the following: Col I Col J Col K Col L 12 4 7 19 How would I write a line of code that will identify that within this array Column L has the largest figure? Many thanks |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identifying the largest number
Good responses, but you don't really need a new function for this.
Assuming that the values are in row 2, you could place the following formula in M2. =ADDRESS(ROW(),COLUMN(INDEX(I2:L2,MATCH(MAX(I2:L2) ,I2:L2,0)))) That would return an absolute reference such as $L$2. You could then use the Mid function to pull out the column letter. =MID(ADDRESS(ROW(),COLUMN(INDEX(I2:L2,MATCH(MAX(I2 :L2),I2:L2,0)))),2, (FIND("$",ADDRESS(ROW(),COLUMN(INDEX(I2:L2,MATCH(M AX(I2:L2),I2:L2,0)))), 2)-2)) Pretty complex formula, but it works. HTH Roger Govier wrote: Hi Pascal I think modifying your posting to MsgBox "Max value is in " & _ Evaluate("=ADDRESS(1,MATCH(MAX(I1:L1),I1:L1,0)+col umn(I1)-1)") will resolve the matter -- Regards Roger Govier "papou" wrote in message ... Phil Forget my answer, it will not return the correct address. I will have another look at it. Cordially Pascal "papou" a écrit dans le message de news: ... Hello Phil Msgbox "Max value is in " & _ Evaluate("=ADDRESS(1,MATCH(MAX(I1:L1),I1:L1,0))") HTH Cordially Pascal "Phil" a écrit dans le message de news: ... I am in need of some help regarding the creation of a simple MAX calculation in my code. The data is something like the following: Col I Col J Col K Col L 12 4 7 19 How would I write a line of code that will identify that within this array Column L has the largest figure? Many thanks |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identifying the largest number
Hi
I hadn't noticed that the Op wanted just the column letter. That being the case then =SUBSTITUTE(ADDRESS(1,MATCH(MAX(I1:L1),I1:L1,0)+CO LUMN(I1)-1,4),"1","") will achieve that in a shorter form -- Regards Roger Govier "JW" wrote in message oups.com... Good responses, but you don't really need a new function for this. Assuming that the values are in row 2, you could place the following formula in M2. =ADDRESS(ROW(),COLUMN(INDEX(I2:L2,MATCH(MAX(I2:L2) ,I2:L2,0)))) That would return an absolute reference such as $L$2. You could then use the Mid function to pull out the column letter. =MID(ADDRESS(ROW(),COLUMN(INDEX(I2:L2,MATCH(MAX(I2 :L2),I2:L2,0)))),2, (FIND("$",ADDRESS(ROW(),COLUMN(INDEX(I2:L2,MATCH(M AX(I2:L2),I2:L2,0)))), 2)-2)) Pretty complex formula, but it works. HTH Roger Govier wrote: Hi Pascal I think modifying your posting to MsgBox "Max value is in " & _ Evaluate("=ADDRESS(1,MATCH(MAX(I1:L1),I1:L1,0)+col umn(I1)-1)") will resolve the matter -- Regards Roger Govier "papou" wrote in message ... Phil Forget my answer, it will not return the correct address. I will have another look at it. Cordially Pascal "papou" a écrit dans le message de news: ... Hello Phil Msgbox "Max value is in " & _ Evaluate("=ADDRESS(1,MATCH(MAX(I1:L1),I1:L1,0))") HTH Cordially Pascal "Phil" a écrit dans le message de news: ... I am in need of some help regarding the creation of a simple MAX calculation in my code. The data is something like the following: Col I Col J Col K Col L 12 4 7 19 How would I write a line of code that will identify that within this array Column L has the largest figure? Many thanks |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identifying the largest number
Many Thanks for all of your suggestions. I think ultimately what I was trying
to do was too complicated for the needs of the workbook. I have used some of what has been proposed but have also created a workround. Thanks again for all of your help. "Phil" wrote: I am in need of some help regarding the creation of a simple MAX calculation in my code. The data is something like the following: Col I Col J Col K Col L 12 4 7 19 How would I write a line of code that will identify that within this array Column L has the largest figure? Many thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Largest number in column | Excel Discussion (Misc queries) | |||
| Largest number 2 | Excel Worksheet Functions | |||
| Largest number | Excel Worksheet Functions | |||
| highlight largest number in a row | Excel Worksheet Functions | |||
| Largest number in a column | Excel Programming | |||
Linear Mode
