# To find the max value +

Solved/Closed
Related:

- To find the max value +
- Max revive cheat pokemon fire red - Guide
- Design a program to print the following pattern sample input: enter the number to be printed: 1 max number of time printed: 3 1 ✓ - Forum - Programming
- Mac mini m1 max release date - Guide
- Swish max 4 full version free download - Download
- Max download manager - Guide

## 6 replies

venkat1926

Jan 1, 2010 at 09:11 PM

- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021

Jan 1, 2010 at 09:11 PM

suppose the data is from A1 to A9

in any cell paste this formula

modify the formula to suit your data

fill some day(numbers) in a sample sheet A1 to A9 and B1 to B9 and test the formula and then only modify it to suit you.

in any cell paste this formula

=INDIRECT("B"&MATCH(MAX(A1:A9),A1:A9,0))

modify the formula to suit your data

fill some day(numbers) in a sample sheet A1 to A9 and B1 to B9 and test the formula and then only modify it to suit you.

venkat1926

Jan 6, 2010 at 05:44 AM

- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021

Jan 6, 2010 at 05:44 AM

Match(x, r, 0)

Set r = Range(Cells(k, 1), Cells(k + 3, 1))

in the fisrt loop r is A1 to A4

x is the maximum of r

match function finds out the row no. in r of the value x

(o the third argument in match function is getting exact value)

then if find out the value of B in that row.

In the subsequent loops there is a small problem when you come to A5 to A8 it will only find the row no. within that range namely A5 to A8 . but what we want the the row number of the overall A range. so that addition of k-1 in the

set rmax=.....................

I hope I made it clear. see help under match in the worksheet. then do some experiments. you will get the hand of it. I am happy you do not want to blindly copy the macro and use it. You want to find out the logic. That will help you to write macros. greetings for happy new year. If you have doubts post back.

Set r = Range(Cells(k, 1), Cells(k + 3, 1))

in the fisrt loop r is A1 to A4

x is the maximum of r

match function finds out the row no. in r of the value x

(o the third argument in match function is getting exact value)

then if find out the value of B in that row.

In the subsequent loops there is a small problem when you come to A5 to A8 it will only find the row no. within that range namely A5 to A8 . but what we want the the row number of the overall A range. so that addition of k-1 in the

set rmax=.....................

I hope I made it clear. see help under match in the worksheet. then do some experiments. you will get the hand of it. I am happy you do not want to blindly copy the macro and use it. You want to find out the logic. That will help you to write macros. greetings for happy new year. If you have doubts post back.

venkat1926

Jan 6, 2010 at 06:51 PM

- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021

Jan 6, 2010 at 06:51 PM

I shall tell you how to modify the macro

copy the original macro safely somewhere so that it is not messed up

in the worksheet

large(a1:a6,2)

will give you the second maximum n A1 to A6

but in vba youi have to use "worksheetfunction "

dim y as double

y=worksheetfunction.large(r,2)

something like this. can you now try to modify the macro.

If there is problem you are free to repost

copy the original macro safely somewhere so that it is not messed up

in the worksheet

large(a1:a6,2)

will give you the second maximum n A1 to A6

but in vba youi have to use "worksheetfunction "

dim y as double

y=worksheetfunction.large(r,2)

something like this. can you now try to modify the macro.

If there is problem you are free to repost

Ya i build the macro to find second max value & so on..

thanks..

U used a command to clear column "C" .

Columns("C:C").Delete

i wanna to clear Column D,E,F while staring macro

i used the same as

Columns("D:D").Delete

Columns("E:E").Delete

but its not clearing the value instead it shift the values to previous column.

do the needs

Mukil..

venkat1926

Jan 2, 2010 at 06:23 AM

- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021

Jan 2, 2010 at 06:23 AM

You have not explained properly You must give the logic behind your needs.

maximum of four cells in which column and also which four cells.

you have two columns A and B.

coumn A has 8 cells, B has 8 cells. which is the maximums value, is it 10 in A1 or 4 in B1.

I suppose your logic is something like this.

you take the first four rows. In this subset in column A. the maximum is 20. the next cell value for 20 is 6. so 6 is to be added in C1 (that is the first row of the subset of column A.

similarly the second subset is 5th to 8 rows. here the maximum of column A in this subset is 52 which has next cell is 3. this 3 is be in column c in the fifth row (the first subset) in column C

next subset is rows 9 to 12 and goes on

Kindly confirm

maximum of four cells in which column and also which four cells.

you have two columns A and B.

coumn A has 8 cells, B has 8 cells. which is the maximums value, is it 10 in A1 or 4 in B1.

I suppose your logic is something like this.

you take the first four rows. In this subset in column A. the maximum is 20. the next cell value for 20 is 6. so 6 is to be added in C1 (that is the first row of the subset of column A.

similarly the second subset is 5th to 8 rows. here the maximum of column A in this subset is 52 which has next cell is 3. this 3 is be in column c in the fifth row (the first subset) in column C

next subset is rows 9 to 12 and goes on

Kindly confirm

u r correct.. i need the same

Take the first four rows. In this subset in column A. the maximum is 20. the next cell value for 20 is 6. so 6 is to be added in C1 (that is the first row of the subset of column A).

similarly the second subset is 5th to 8 rows. here the maximum of column A in this subset is 52 which has next cell is 3. this 3 is be in column c in the fifth row (the first subset) in column C

next subset is rows 9 to 12 and goes on

kindly help me in this ...

Didn't find the answer you are looking for?

Ask a question
venkat1926

Jan 4, 2010 at 08:11 PM

- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021

Jan 4, 2010 at 08:11 PM

Of course i can have a formula which is becoming cumbersome and complex. so I have given a macro.

suppose there are two maximums in a subset e.g. a1 to a4 then it will take the corresponding B value only for the first maximum .

Sub test() Dim j As Integer, k As Integer, r As Range, x As Double, rmax As Range Worksheets("sheet1").Activate Columns("C:C").Delete j = Range("A1").End(xlDown).Row For k = 1 To j Step 4 Set r = Range(Cells(k, 1), Cells(k + 3, 1)) x = WorksheetFunction.Max(r) Set rmax = Cells(WorksheetFunction.Match(x, r, 0), "b") Cells(k, "C").Value = rmax Next k End Sub

suppose there are two maximums in a subset e.g. a1 to a4 then it will take the corresponding B value only for the first maximum .

Hi Venkat

Thanks for ur macro

but its not working properly.

for first set its fine - gives correct value

from the second set(ranges(5:8) ) onwards - its give the value of the fourth row.

the max is working fine .the probs. with the match function.

the result of my excel

A B C

10 1 2

60 2

30 3

40 B

50 5 B

60 6

70 7

80 8

90 9 B

100 10

110 11

120 12

130 13 B

140 14

150 15

160 16

170 17 B

180 18

190 19

200 20

-- look into it....

Thanks for ur macro

but its not working properly.

for first set its fine - gives correct value

from the second set(ranges(5:8) ) onwards - its give the value of the fourth row.

the max is working fine .the probs. with the match function.

the result of my excel

A B C

10 1 2

60 2

30 3

40 B

50 5 B

60 6

70 7

80 8

90 9 B

100 10

110 11

120 12

130 13 B

140 14

150 15

160 16

170 17 B

180 18

190 19

200 20

-- look into it....

venkat1926

Jan 5, 2010 at 08:44 PM

- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021

Jan 5, 2010 at 08:44 PM

yes there is a small error in the macro . sorry. try this revised macro

I have indicated where the modification is done in the macro as a comment.\\

please confirm whether it works ok.

I have indicated where the modification is done in the macro as a comment.\\

please confirm whether it works ok.

Sub test() Dim j As Integer, k As Integer, r As Range, x As Double, rmax As Range Worksheets("sheet1").Activate Columns("C:C").Delete j = Range("A1").End(xlDown).Row For k = 1 To j Step 4 Set r = Range(Cells(k, 1), Cells(k + 3, 1)) 'msgbox r.Address x = WorksheetFunction.Max(r) 'msgbox x Set rmax = Cells(WorksheetFunction.Match(x, r, 0) + k - 1, "b") 'the modification is in the above line 'msgbox rmax.Address Cells(k, "C").Value = rmax Next k End Sub

mukil
>
venkat1926

Jan 5, 2010 at 11:37 PM

- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021

Jan 5, 2010 at 11:37 PM

Hi Venkat

Thanks a lot.

Its working fine.

can u plz explain the match function

Set rmax = Cells(WorksheetFunction.Match(x, r, 0) + k - 1, "b")

i can able to get other functions.

from

mukil

Thanks a lot.

Its working fine.

can u plz explain the match function

Set rmax = Cells(WorksheetFunction.Match(x, r, 0) + k - 1, "b")

i can able to get other functions.

from

mukil

Jan 1, 2010 at 11:49 PM

i need some more help form u.

actually i have a set of values in a column and i have 2 compare only by four,four cells and find the max & the value next to that cell.

i.e

@ my excel i have

A B C

10 4 6

20 6

15 7

05 9

45 1 3

35 5

52 3

20 4 ..... like that goes on..

I need the values in column "C" as i specified..