VBA Macro Highest Value Copy

[hughhuyton]

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:

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

 

[hiker95]

hughhuyton,

Thanks for the workbook.

Here is a macro solution for you to consider that is based on the structure of your linked workbook.

You can change the raw data worksheet name in the macro.

1. Is the 5040 always in column A?

2. TR fixed pricing always in column A?

Sample raw data (not all columns are shown for brevity):

Excel 2007
	A	B	U	V
1		5	100
2	10	112.5	92.5
3	9	72	92.5
4	8	25	185
5	7	-26	185
6	6	-79.5	277.5
7	5	-130	370
8	4	-171.5	555
9	3	-193.5	647.5
10	2	-187	647.5
11	1	-130	462.5
12	TR fixed pricing	-708	3515
13
14
15
16
17
18
19
20	5040
21
22
23
24
25
26
27
28
29
30
31
Sheet1

And, after the macro:

Excel 2007
	A	B	U	V
1		5	100
2	10	112.5	92.5
3	9	72	92.5
4	8	25	185
5	7	-26	185
6	6	-79.5	277.5
7	5	-130	370
8	4	-171.5	555
9	3	-193.5	647.5
10	2	-187	647.5
11	1	-130	462.5
12	TR fixed pricing	-708	3515
13
14
15
16
17
18
19
20	5040
21	360
22	427.5
23	495
24	562.5
25	637.5
26	712.5
27	765
28	787.5
29	750
30	542.5
31
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub FindHighestValues()
' hiker95, 12/12/2016, ME980208
Dim trfp As Range, c As Range, lc As Long, nr As Long, mx As Double
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  lc = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
  Set trfp = .Columns(1).Find("TR fixed pricing*", LookAt:=xlWhole)
  If Not trfp Is Nothing Then
    nr = trfp.Row + 9
    For Each c In .Range("A2:A" & trfp.Row - 1)
      mx = Application.Max(.Range(.Cells(c.Row, 2), .Cells(c.Row, lc)))
      .Range("A" & nr).Value = mx
      nr = nr + 1
    Next c
  End If
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the FindHighestValues macro.

 

[Eric W]

Re: VBA Macro Highest Value

As you've seen, the Set won't work because the Max function returns a value, not a location. If you really needed to get the location, you could use a Find like this:

Set HVCell = Intersect(Range("A1:T20"), Rows(MyRow)).Find(HighestValue)

However, you really don't want to do that in this case. Since you're just pasting the value, it's better to just move the value. Copying and pasting is very inefficient. Only do it if you want the formatting as well. Just do:

Range("A20").Value = HighestValue

 

[hughhuyton]

wow thank you so much hiker95 for your detailed reply! I tried to run the macro without success but I think thats because i'm using Excel 2016 on a mac With regards to your first and second questions, the copied results of the highest values do not always have to be in column A, in fact I want to paste them into a different sheet but said this to simplify the problem. With regards to the TR fixed pricing, it does not always have to be in column A, in fact it can just be taken out.

One of the other codes I had used which found the highest value is shown below, but that would mean I would have to make the maximum value equal to a range. Is this feasible?

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff; min-height: 13.0px}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff; min-height: 13.0px}span.s1 {color: #000000}span.s2 {color: #011993}</style>

Dim MyRow As Integer
Dim HighestValue As Integer




Sub MaximumValue()




    For MyRow = 2 To 13
        
    HighestValue = WorksheetFunction.Max(Intersect(Range("A1:T20"), Rows(MyRow)))


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




    Next MyRow
   
End Sub

Again enormous thanks for the help!

 

[RoryA]

@hugh
I've merged your two threads together. Please don't post the same question twice in future. Thanks.

 

[hughhuyton]

lesson learnt! Thanks, I'm new to this as you probably guessed

 

[hiker95]

wow thank you so much hiker95 for your detailed reply! I tried to run the macro without success but I think thats because i'm using Excel 2016 on a mac

hughhuyton,

Thanks for the feedback.

You are very welcome.

I have no experience with a Mac.

In the future when asking for help you should add to your thread titles that you are using a Mac.


Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[hughhuyton]

Re: VBA Macro Highest Value

<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}span.s1 {color: #000000}span.s2 {color: #011993}</style>Hi Eric, I understand that it is more efficient just to move the value, but for what I want to do after it makes more sense for me to copy and paste it. The code you gave works for the first row, however after that I get the error that the 'Object variable or With block variable not set' for the "HVCell" but I thought that it is. I basically need it to set the range HVCell equal to the highest value on each row.

<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: #fffb00}p.p5 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff; min-height: 13.0px}span.s1 {color: #000000}span.s2 {color: #011993}</style>
Dim MyRow As Integer
Dim HighestValue As Integer
Dim HVCell As Range


Sub MaximumValue()




    For MyRow = 2 To 13
        


    HighestValue = WorksheetFunction.Max(Intersect(Range("B2:U12"), Rows(MyRow)))


    Set HVCell = Intersect(Range("B2:U12"), Rows(MyRow)).Find(HighestValue)
    
    HVCell.Copy


    Range("A20").PasteSpecial Paste:=xlPasteValues
    
    Next MyRow
   
End Sub

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Helvetica Neue'}</style>

 

[Eric W]

Re: VBA Macro Highest Value

As a template, that code works fine for me. However, there is a problem with the specifics. Your loop is looking at rows 2 to 13, but the range in the Intersect is B2:U12 (maximum row 12). So there will be no intersection when you get to row 13, which will cause an error. You can either fix the references to match, or if they are dynamic, add some error handling.

 

[hughhuyton]

Re: VBA Macro Highest Value

Understood, however I am still receiving the error after the first row, is this because I am using a mac? Thanks Eric

 

[Eric W]

Re: VBA Macro Highest Value

Unlikely that being on a Mac is a problem. There are some specific differences between Mac VBA and PC VBA, but this code is pretty basic. My next guess is that your second row does not have a Max value. Everything on it is either empty or a text value. You can check for that with something like:

Sub MaximumValue()
Dim MyRow As Integer
Dim HighestValue As Integer
Dim HVCell As Range

    For MyRow = 2 To 13
        
        HighestValue = WorksheetFunction.Max(Intersect(Range("B2:U13"), Rows(MyRow)))
        Set HVCell = Intersect(Range("B2:U13"), Rows(MyRow)).Find(HighestValue)
    
        If HVCell Is Nothing Then
            Range("A20").Value = "No max found"
        Else
            HVCell.Copy
            Range("A20").PasteSpecial Paste:=xlPasteValues
        End If
    
    Next MyRow
   
End Sub