Copying Value next to the highest value in column A

[kshitij_dch]

Hi ,

I was working on a sheet where i have some values in column A , in the last cell of column A is the SUM of the values.

I am looking for a code that will copy the sum of values next to the highest value in column A , for an example :


[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Amount[/TD]
[TD="class: xl63, width: 64"]Value[/TD]
[/TR]
[TR]
[TD="class: xl63"]1000[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]2000[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]15000[/TD]
[TD="class: xl63"]24000[/TD]
[/TR]
[TR]
[TD="class: xl63"]1000[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]2000[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]3000[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]24000[/TD]
[TD="class: xl63"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Is this what you mean? Formula in B2 is copied down to B7.

Excel Workbook
	A	B
1	Amount	Value
2	1000
3	2000
4	15000	24000
5	1000
6	2000
7	3000
8
9	24000
Highest

 

[kshitij_dch]

Yes exactly i wanted , Thank you Peter , is it possible i can convert this into macro code ?

 

[Peter_SSs]

Yes exactly i wanted , Thank you Peter , is it possible i can convert this into macro code ?

Is the total formula already at the bottom of the column?

If not, should the macro insert that formula/value too?

 

[kshitij_dch]

yes total formula is already present.

 

[Peter_SSs]

In column B, do you want just the value(s) (ie number) inserted, or do you want formulas so that if the numbers in column A change, column B will automatically adjust?

 

[My Aswer Is This]

Try this:

Sub Largest_Value()
'Modified 3-4-18 12:15 AM EST
Dim ans As Long
Dim Lastrow As Long
Dim mysum As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
mysum = Range("A" & Lastrow).Value
ans = Application.WorksheetFunction.Max(Range("A1:A" & Lastrow - 1))
Dim SearchString As String
Dim SearchRange As Range
SearchString = ans
Set SearchRange = Range("A1:A" & Lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
SearchRange.Offset(0, 1).Value = mysum
End Sub

 

[Peter_SSs]

In column B, do you want just the value(s) (ie number) inserted, or do you want formulas so that if the numbers in column A change, column B will automatically adjust?

This covers both - refer to the comment in the code.
Note that this code ..
- allows for the case where the maximum value in column A occurs more than once (noting that your sample data had repeating values)
- also works if the numbers in column A are not necessarily all whole numbers.

Sub MarkHighest()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row - 1)
    .FormulaR1C1 = "=if(RC[-1]=max(R2C[-1]:R" & .Row + .Rows.Count - 1 & "C[-1]),R" & .Row + .Rows.Count & "C[-1],"""")"
    .Value = .Value   '<- Remove this line if you want to retain the formulas in column B
  End With
End Sub

 

[kshitij_dch]

Thanks a lot its working fine

 

[Peter_SSs]

Thanks a lot its working fine

Glad you had a successful outcome. Thanks for letting us know.