VBA Macro Highest Value Copy

hughhuyton

New Member
Joined
Dec 11, 2016
Messages
16
I'm trying to do a macro which has a loop in a loop that finds the highest value in each row of a table and then copies the highest value to another part of the sheet. It successfully finds the maximum value in the first row but after that it copies the wrong value (not the highest). This is what my code looks like:
Code:
Dim HighestValue As Integer
Dim VCounter As Integer
Dim HCounter As Integer

Sub maximumvalue()
   
For HCounter = 0 To 9

Range("A1").Offset(HCounter, 0).Select
    
    HighestValue = 0
    
    For VCounter = 1 To 20 Step 1

    Range("A2").Offset(HCounter, VCounter).Select

    If Range("A2").Offset(HCounter, VCounter).Value > HighestValue Then

    HighestValue = Range("A2").Offset(HCounter, VCounter).Value

    End If

If Range("A2").Offset(HCounter, VCounter).Value = HighestValue Then

    HighestValue = Range("A2").Offset(HCounter, VCounter).Copy

    End If

If Range("A2").Offset(HCounter, VCounter).Value = HighestValue Then

Range("A20").PasteSpecial Paste:=xlPasteValues

End if

    Next VCounter

MsgBox "The highest Value is " & HighestValue

Next HCounter


End Sub
 
Last edited by a moderator:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
hughhuyton,

Welcome to the MrExcel forum.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com


When posting VBA code, please use Code Tags - like this:

[code=rich]

Paste your code here.

[/code]
 
Last edited:
Upvote 0
VBA Macro Highest Value

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454545}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454545; min-height: 14.0px}</style>I'm trying to do a macro which has a loop in a loop that finds the highest value in each row of a table and then copies the maximum value to a table further down the sheet. My problem is that my macro successfully finds the maximum value in some of the rows but not all of them (see screenshot). Im using microsoft 2016 (mac)




Code:
Dim HighestValue As Integer
Dim VCounter As Integer
Dim HCounter As Integer


Sub maximumvalue()
   
For HCounter = 0 To 9


Range("A1").Offset(HCounter, 0).Select
    
    HighestValue = 0
    
    For VCounter = 1 To 20 Step 1


    Range("A2").Offset(HCounter, VCounter).Select


    If Range("A2").Offset(HCounter, VCounter).Value > HighestValue Then


    HighestValue = Range("A2").Offset(HCounter, VCounter).Value


    End If


    Next VCounter


MsgBox "The highest Value is " & HighestValue


Next HCounter




End Sub

Link to Workbook file: https://www.dropbox.com/s/o0axxjegrry7cc5/HighestValue%20Macro.xlsm?dl=0
 
Upvote 0
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454545}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454545; min-height: 14.0px}</style>
Code:
Dim HighestValue As Integer
Dim VCounter As Integer
Dim HCounter As Integer


Sub maximumvalue()
   
For HCounter = 0 To 9


Range("A1").Offset(HCounter, 0).Select
    
    HighestValue = 0
    
    For VCounter = 1 To 20 Step 1


    Range("A2").Offset(HCounter, VCounter).Select


    If Range("A2").Offset(HCounter, VCounter).Value > HighestValue Then


    HighestValue = Range("A2").Offset(HCounter, VCounter).Value


    End If


    Next VCounter


MsgBox "The highest Value is " & HighestValue


Next HCounter




End Sub

The url is: https://www.dropbox.com/s/o0axxjegrry7cc5/HighestValue Macro.xlsm?dl=0
 
Upvote 0
Re: VBA Macro Highest Value

The only thing I see immediately is that if you have negative numbers you could get incorrect results. You could also simplify your macro to something like:

Code:
Sub MaximumValue()
Dim MyRow As Long, HighestValue As Double

    For MyRow = 1 To 10
        HighestValue = WorksheetFunction.Max(Intersect(Range("A1:T20"), Rows(MyRow)))
        MsgBox "The highest value on row " & MyRow & " is: " & HighestValue
    Next MyRow
    
End Sub
 
Upvote 0
Re: VBA Macro Highest Value

Hi Eric, thank you so much this works great! However if I also want to copy the highest value would I have to set a variable equal to the address of the highest value and then copy it from there? This is what the code looks like

Code:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff; min-height: 13.0px}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff; min-height: 13.0px}p.p5 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #fffb00}span.s1 {color: #000000}span.s2 {color: #011993}span.s3 {background-color: #ffffff}</style>Dim MyRow As Integer
Dim HighestValue As Integer
Dim HVCell As Range


Sub MaximumValue()


    For MyRow = 1 To 10
        
    WorksheetFunction.Max (Intersect(Range("A1:T20"), Rows(MyRow)))


    MsgBox "The highest value on row " & MyRow & " is: " & HighestValue


Set HVCell = WorksheetFunction.Max(Intersect(Range("A1:T20"), Rows(MyRow)))


HVCell.Copy


Range("A20").PasteSpecial Paste:=xlPasteValues


    Next MyRow
    
    End Sub
 
Upvote 0
hughhuyton,

I have had problems in the past when attempting to download an Excel file with macros, with the xlsm file extension.

Please rename the workbook using the xlsx file extension, and, remove all macros, and, then repost on dropbox.
 
Last edited:
Upvote 0
Hi hiker95

I have changed the file so that it is a xlsx file:

https://www.dropbox.com/s/8p1hrczduwick89/HighestValue Macro.xlsx?dl=0

My code to find the highest value in each row is shown below but I don't know how to get it to copy the highest value in each row and paste it further down the sheet.
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454545}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454545; min-height: 14.0px}</style>
Code:
Sub MaximumValue()
Dim MyRow As Long, HighestValue As Range


    For MyRow = 1 To 10
        Set HighestValue = WorksheetFunction.Max(Intersect(Range("A1:T20"), Rows(MyRow)))
        HighestValue.Select


    Next MyRow
    
End Sub
 
Upvote 0
Re: VBA Macro Highest Value

Hi Eric, sorry about this was not sure if I had to start a new thread. How would I go about trying to reference the cell of the highest value?
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top