0

I have a sheet where the first two columns have row and column addresses and the third column has integer values.

I would like to extract the highest value and it's corresponding address from columns 1 and 2 into a separate list on the sheet.

There may be multiple equal highest values in the third column. How do I get all of those into my list.

I'm a fairly new at Excel VBA.

Community
  • 1
  • 1
Adam B
  • 93
  • 1
  • 3
  • 13
  • Have you tried using the Rank() worksheet function or the Large() worksheet function? You can probably do it with one of those and sorting/filtering on those results – SmileyFtW Oct 31 '19 at 21:21

1 Answers1

2

This could be a start.

Sub maxIntAndOtherStuff()
    Dim rng As Range, c As Range, i As Integer, x As Long, y As Long

    rw = 1: cl = 3 'Apply starting row & column

    Set rng = Range(Cells(rw, cl), Cells(Rows.Count, cl).End(xlUp))

    For Each c In rng
        If c.Value >= i Then
            i = c.Value
        End If
    Next c

    y = 9 'Apply column number for output
    x = Cells(Rows.Count, y).End(xlUp).Offset(1).Row 'Finds the first empty row in that column

    For Each c In rng
        If c = i Then
            Cells(x, y).Resize(, 3).Value = c.Offset(, -2).Resize(, 3).Value
            x = x + 1
        End If
    Next c
End Sub
AsUsual
  • 524
  • 2
  • 8