VBA-将范围从最大范围设置为 [英] VBA - Set Range from Max in range

问题描述

我想知道使用 WorksheetFunction.Max 函数后是否可以设置范围.因此，如果最大范围是2015年12月31日，我可以将值恢复为单元格范围，即"D10" .这是我到目前为止的代码.

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()

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)

结束字幕

因此，简而言之，我需要根据每个合约的结束日期最后一个净值， I列.

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

推荐答案

这里是一种设置方式，具体取决于您的设置-如果无法正常运行，则应发布整个代码.(还请注意非唯一的最大值.)

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-将范围从最大范围设置为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！