28887

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 <a href="https://i.stack.imgur.com/o54Lt.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/o54Lt.png" data-original="https://i.stack.imgur.com/o54Lt.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

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

Answer1:

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)

Recommend

  • VBA Nested If, Then, Else Loop That Copies Non-Matching Entries
  • Extract outlook message body text with VBA from Excel
  • How does one get the UserForm where MSForms.ComboBox Object is located?
  • Get pivotTable handle from cell in vba
  • {“Exception from HRESULT: 0x800A03EC”} at Microsoft.Office.Interop
  • macro that auto-executes when sheet is opened
  • remove non-ASCII characters (using Microsoft.Office.Interop.Excel)
  • Incorrect VBA Overflow using Doubles (Excel)
  • how to export table as excel with 10000 to 40000 rows
  • Need to add paste special condition a copy.destination for loop
  • How to do Calculation in Excel Cell upon leaving the cell
  • How to adjust chart size to fit page size using Excel VBA?
  • EPPlus throwing Argument Exception (Negative row and column numbers are not allowed)
  • Excel named ranges to make incrementing headers and sub headers
  • How can I preserve a pandas multi-index between a to_excel() and a read_excel()?
  • Multiplying arrays with scalars and adding in VBA
  • Bloomberg Data… Why doesn't Application.WorksheetFunction.BDH work?
  • What's a robust method in R for importing from and exporting data to Excel?
  • Unable to delete a worksheet using EPPlus
  • How to read empty cells in PHPExcel without skipping values?
  • Formula in Excel that references another Excel file based on cell reference
  • Excel Range in C# using interop.Excel. Range both empty and not empty?
  • VBA vlookup with defined range and file from other workbook
  • LINQ to NHibernate WHERE EXISTS IN
  • How do I pass worksheet and ranges as variables?
  • Stored Procedure with multiple IN Parameter
  • Why would a Scala Worksheet using Scala-IDE give this as an error?
  • Can you build a truly RESTful service that takes many parameters?
  • Can I use worksheet_change for a specific column only?
  • Get the last date of each month in a list of dates in Python
  • How to calculate networkdays minus holidays between 2 dates
  • Calling Worksheet functions from vba in foreign language versions of Excel
  • Use of this Javascript
  • Linq Objects Group By & Sum
  • How to format a variable of double type
  • Buffer size for converting unsigned long to string
  • embed rChart in Markdown
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app