Hello, I am needing some VBA code help. I need to extract the column number of the most recent date in an entire row. I want to be able to save the column number in a variable that will be used later. I've been messing around with address(match(max))) but I don't need the entire address just the column number. I also know I can get the column number of a cell with cellname.column but how do I get excel to look through the row to find the most recent date? Thanks for the help
Find column number of most recent date in row to be used as a variable
-
jlucas1188 -
December 8, 2020 at 1:44 PM -
Thread is marked as Resolved.
-
-
-
If you data is on row 1, here is some sample code for you to work with and modify
Code
Display MoreOption Explicit Sub MaxDate() Dim lc As Long, x As Date, tCol As Long, i As Long lc = Cells(1, Columns.Count).End(xlToLeft).Column For i = 1 To lc If Cells(1, i) = Application.WorksheetFunction.Max(Range(Cells(1, 1), Cells(1, lc))) Then tCol = i End If Next i MsgBox ("Column is " & tCol) End Sub
-
An alternative using the Range.Find function to find the max date value is below.
This assumes row under ActiveCell in the ActiveSheet is to be searched.
Code
Display MoreSub FindMaxDateInRow() Dim wsSht As Worksheet Dim lRow As Long, lEnd As Long Dim lMax As Long Dim rRange As Range 'assume activesheet is to be used Set wsSht = ActiveSheet 'assume row under activecell is to be searched lRow = ActiveCell.Row With wsSht 'determine last column in this row lEnd = .Cells(lRow, .Columns.Count).End(xlToLeft).Column 'create a range representing the row 'this includes column A, adjust if necessary 'any text in column A will however be ignored for Max function Set rRange = .Range(.Cells(lRow, "A"), .Cells(lRow, lEnd)) 'work out the maximum date value lMax = Application.WorksheetFunction.Max(rRange) 'search for this value in the row Set rRange = rRange.Find(CDate(lMax), LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) 'jump to the max value cell and display a message If Not rRange Is Nothing Then Application.Goto wsSht.Cells(lRow, rRange.Column), False MsgBox "Max date found in" & vbLf & "Row: " & lRow & " Column: " & rRange.Column End If End With Set rRange = Nothing Set wsSht = Nothing End Sub
-
-
Three propositions ...
Waiting for your feedback ...
Feel free to share your comments
-
-
15 DAYS after your initial question ....
Is there a specific obstacle which is preventing you from acknowledging receipt of the various proposals ...
and giving your feedback ...
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!