VBA to find value MAX, MIN and LAST_DAY

[excel_newbie86]

Book1
	A	B	C	D
1	File name	Value
2	A12345-99902001-12345678-20200730-xx-y-zz.xlsx	100
3	A12345-99902001-12345678-20200731-xx-y-zz.xlsx	120
4	A12345-99902001-12345678-20200630-xx-y-zz.xlsx	140
5	A12345-99902001-12345678-20200630-xx-y-zz.xlsx	141	MAX
6	A12345-99902001-12345678-20200620-xx-y-zz.xlsx	101
7	A12345-99902001-12345678-20200621-xx-y-zz.xlsx	95
8	A12345-99902001-12345678-20200829-xx-y-zz.xlsx	99
9	A12345-99902001-12345678-20200830-xx-y-zz.xlsx	81
10	A12345-99902001-12345678-20200831-xx-y-zz.xlsx	80	MIN	LAST_DAY
Sheet1

Hi all

I have data in sheet1 with column(A) is file name, column(B) is value

I want to have macro to do that:
- Find MAX value in coumn(B) then add "MAX" to column(C)
- Find MIN value in coumn(B) then add "MIN" to column(C)
- Find the last day in coumn(A) then add "MAX" to column(A)
the day in column(A) in character from 26 to 33 => With my data the last_day must be in file A12345-99902001-12345678-20200831-xx-y-zz.xlsx ==> last day is 20200831

Thanks./.

 

[Peter_SSs]

See if this does what you want. Test with a copy of your data.

Sub MinMaxLast()
  Dim lr As Long
  
  lr = Range("A" & Rows.Count).End(xlUp).Row
  With Range("C2:C" & lr)
    .FormulaR1C1 = "=IF(RC[-1]=MAX(R2C[-1]:R" & lr & "C[-1]),""MAX"",IF(RC[-1]=MIN(R2C[-1]:R" & lr & "C[-1]),""MIN"",""""))"
    .Value = .Value
    With .Offset(, 1)
      .FormulaR1C1 = "=IF(MID(RC[-3],26,8)-AGGREGATE(14,6,MID(R2C[-3]:R" & lr & "C[-3],26,8)+0,1)=0,""LAST_DAY"","""")"
      .Value = .Value
    End With
  End With
End Sub

 

[excel_newbie86]

Thanks @Peter_SSs

Code work well

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.