VBA - Set Range from Max in range

Question

I was wondering if there is a way to set a range after using the WorksheetFunction.Max function. So, if the range max is 12/31/2015, can I revert the value back to a cell range, i.e. "D10". This is the code that I have so far.

Sub NetValue()

Dim lngLastRow As Long, rngCell As Range, rngRange As Range, _
lngMin As Long, lngMax As Long, lngPreviousRow As Long, _
raw As Worksheet, data As Worksheet, dLRow As Double, endDate As Double, _
r As Range, n As Long

lngLastRow = lastRow(column_to_check:=2)

Set raw = Worksheets("Raw")
Set data = Worksheets("Data")
Set rngRange = raw.Range(raw.Cells(2, 1), raw.Cells(lngLastRow + 1, 1))
dLRow = data.Range("A1", data.Range("A1").End(xlDown)).Rows.Count

raw.Range("J:J").EntireColumn.Insert
raw.Range("C:E").EntireColumn.NumberFormat = "mm/dd/yyyy"

For Each rngCell In rngRange

    If Len(rngCell) > 0 Then

        If lngPreviousRow > 0 And (rngCell.Row - 1 <> lngPreviousRow) Then
            raw.Cells(lngPreviousRow, 10) = s.Cells(n).Offset(0, 6)
        End If

        If (rngCell.Row = 1) Or lngPreviousRow = (rngCell.Row - 1) Then
            Set r = raw.Range(rngCell.Offset(0, 1), rngCell(0, 2))
            Set s = raw.Range(rngCell.Offset(0, 2), rngCell(0, 3))
            lngMin = WorksheetFunction.Min(r)
            lngMax = WorksheetFunction.Max(s)
            m = Application.Match(lngMin, r, 0)
            n = Application.Match(lngMax, s, 0)
            raw.Cells(rngCell.Row, 10) = s.Cells(n).Offset(0, 6)
        End If

        lngPreviousRow = rngCell.Row
        Set r = raw.Range(rngCell.Offset(0, 1), rngCell(0, 2))
        Set s = raw.Range(rngCell.Offset(0, 2), rngCell(0, 3))
        lngMin = WorksheetFunction.Min(r)
        lngMax = WorksheetFunction.Max(s)
        m = Application.Match(lngMin, r, 0)
        n = Application.Match(lngMax, s, 0)

    Else
        Set r = raw.Range(rngCell.Offset(0, 1), rngCell(0, 2))
        Set s = raw.Range(rngCell.Offset(0, 2), rngCell(0, 3))
        lngMin = WorksheetFunction.Min(r)
        lngMax = WorksheetFunction.Max(s)
    End If
Next rngCell

Cells(lngPreviousRow, 10) = s.Cells(n).Offset(0, 6)

End Sub

So, in short, I need the last Net Value, Column I, based on the end date of each contract.

SJR SJR · Accepted Answer · 2017-11-02T15:53:16

Here is one way, depending your set up - you should post your whole code if it doesn't work. (Also watch out for non-unique max values.)

Dim n As Long, enddate, r As Range

Set r = Range(rngCell.Offset(0, 1), rngCell.Offset(0, 2))
enddate = WorksheetFunction.Max(r)
n = Application.Match(enddate, r, 0)

raw.Cells(rngCell, 10) = r.Cells(n).Offset(0, 5)

VBA - Set Range from Max in range

1 Answers