Find max and min date in a column which needs to be found in row 1

[Muktar888]

Hi All,

Im am trying to use the find function to return the column letter in order to fetch the latest date in that column :

Sub find_latest_date()

Dim max_dat As Date, min_dat As Date
Dim last_lin As Long
Dim ws As Worksheet
Dim rfind As Range
Set ws = ThisWorkbook.Worksheets("Paste")
Set ws2 = ThisWorkbook.Worksheets("Control")

last_lin = ws.Cells(Rows.Count, 1).End(xlUp).Row

With Range("A1:FF1")
 Set rfind = .Find(What:="ART start date", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
 If Not rfind Is Nothing Then
Routing_column = Routing_column = Split(rfind.Address, "$")(1)

End If
 End With


max_dat = Application.WorksheetFunction.Max(ws.Range("rfind" & "1" & "FF" & last_lin))


Call remove_nonblank_Last_ART_VL_Date

End Sub

Still an amateur here so any guidance would be useful, thanks!

 

[Joe4]

You have some issue with this line here:

max_dat = Application.WorksheetFunction.Max(ws.Range("rfind" & "1" & "FF" & last_lin))

Note that in VBA, everything enclosed in double-quotes is treated as literal text. So all variables should NOT be enclosed in double quotes.
So you need to refer to your rfind variable like this: rfind and not like this: "rfind"

However, I don't think that is what you want to use anyway. It looks like you want the column letter there, which is your Routing_column variable. rfind is a range.
Also, when creating the range address, if it is more than one cell, you need to separate the starting and ending cells with a semi-colon, like this: A1:J10.
So that range reference probab;y needs to look something like:

Range(Routing_column & "1:FF" & last_lin)

Note. If you run into trouble building your range, it is always good to set it up as a variable, and then use a MsgBox to see if you are building the range string correctly, i.e.

Dim rng as String
rng = Routing_column & "1:FF" & last_lin
MsgBox rng
max_dat = Application.WorksheetFunction.Max(ws.Range(rng))

 

[Muktar888]

You have some issue with this line here:

max_dat = Application.WorksheetFunction.Max(ws.Range("rfind" & "1" & "FF" & last_lin))

Note that in VBA, everything enclosed in double-quotes is treated as literal text. So all variables should NOT be enclosed in double quotes.
So you need to refer to your rfind variable like this: rfind and not like this: "rfind"

However, I don't think that is what you want to use anyway. It looks like you want the column letter there, which is your Routing_column variable. rfind is a range.
Also, when creating the range address, if it is more than one cell, you need to separate the starting and ending cells with a semi-colon, like this: A1:J10.
So that range reference probab;y needs to look something like:

Range(Routing_column & "1:FF" & last_lin)

Note. If you run into trouble building your range, it is always good to set it up as a variable, and then use a MsgBox to see if you are building the range string correctly, i.e.

Dim rng as String
rng = Routing_column & "1:FF" & last_lin
MsgBox rng
max_dat = Application.WorksheetFunction.Max(ws.Range(rng))

Thanks for the pointers! it helped me fix the issues and now works as expected!

Thanks @Joe4!!! Solved!

 

[Joe4]

You are welcome. Glad I could help!