find max value using column name || userforms VBA

[THE_NEW_XL_GUY]

Hi guys,

Need some help in finding max value in a column with column name. like search max value in a column with the column name I provide.

conditions: column May or May not be present in sheet.
there are 3 or 4 columns with same column name and I need to check all.


current code I have just finds max value in range.

Private Sub Cmd_tt_today_bps_Click()
 
Dim fNameAndPath As Variant, wb As Workbook, ws As Worksheet

fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.CSV), *.CSV", Title:="Select File To Be Opened")
If fNameAndPath = False Then Exit Sub
Set wb = Workbooks.Open(fNameAndPath)
max_num = Application.WorksheetFunction.Max(Range.Name) 
wb.Close savechanges:=False

Really need some directions on this. thanks in advance.

 

[Fluff]

How about

   Dim Fnd As Range
   Dim Cnt As Long
   Dim Qty As Long
   Dim NewMax As Variant
   
   Set Fnd = Range("A1")
   Qty = WorksheetFunction.CountIf(Rows(1), "* in*")
   ReDim NewMax(1 To Qty)
   For Cnt = 1 To Qty
      Set Fnd = Rows(1).Find("* in*", Fnd, , xlWhole, , , False, , False)
      NewMax(Cnt) = Application.WorksheetFunction.Max(Fnd.EntireColumn)
   Next Cnt
   MsgBox Join(NewMax, ", ")

 

[THE_NEW_XL_GUY]

its working, its showing all 10 columns max values I.E 10 max values . what if I want THE max among those 10 values to be showed? and also help me to store the one max value among all max value in variable like the_max_value as I want to pass to a cell.

 

[Fluff]

That's exactly what the code I posted in post#3 should do.

 

[THE_NEW_XL_GUY]

sorry if am dragging this, In post #3 code, where to provide max_num value?. we have not stored any values in max_num to compare with newmax right?


Dim Fnd As Range
Dim Cnt As Long
Dim Qty As Long
Dim NewMax As Variant

Set Fnd = Range("A1")
Qty = WorksheetFunction.CountIf(Rows(1), tb1.Value)
For Cnt = 1 To Qty
Set Fnd = Rows(1).Find(tb1.Value, Fnd, , xlWhole, , , False, , False)
NewMax = Application.WorksheetFunction.Max(Fnd.EntireColumn)
If NewMax > max_num Then max_num = NewMax
Next Cnt

 

[Fluff]

For the 1st col there will be no value in max_num so on this line

If NewMax > max_num Then max_num = NewMax

NewMax will be greater then max_num so we make max_num = NewMax.
For each subsequent col if NewMax is greater than max_num, we store that new number, otherwise it's left as is.

 

[THE_NEW_XL_GUY]

okay.. Got it!!

here is the code I modified to my need.

Private Sub Cmd_max_Click()
 
   Dim fNameAndPath As Variant 
   Dim wb As Workbook 
   Dim ws As Worksheet
   Dim Fnd As Range
   Dim Cnt As Long
   Dim Qty As Long
   Dim newmax As Variant
   Dim dummy As Variant
   
fNameAndPath = Application.GetOpenFilename(fileFilter:="Excel Files (*.CSV), *.CSV", Title:="Select File To Be Opened")
If fNameAndPath = False Then Exit Sub
Set wb = Workbooks.Open(fNameAndPath)
Set Fnd = Range("A1")
 Qty = WorksheetFunction.CountIf(Rows(1), "* in*")
 For Cnt = 1 To Qty
 Set Fnd = Rows(1).Find("* in*", Fnd, , xlWhole, , , False, , False)
 newmax = Application.WorksheetFunction.Max(Fnd.EntireColumn)
 If newmax > dummy Then dummy = newmax
 
 Next Cnt
 
 nm = newmax ' I have declared nm outside of the sub and am assigning newmax value to nm, as value is used in another button.

and above code still shows max value of last column with the name "* in*"

 

[THE_NEW_XL_GUY]

tried all the ways, still the code is finding max value in the last column. thanks a ton for helping me this for! Il find outa way! cheers!

 

[Fluff]

The last line should be

nm = dummy

 

[THE_NEW_XL_GUY]

oh right! sorry noob mistake! works like charm now. thanks for making me understand code! Gracias mate! you the best!

 

[Fluff]

Glad to help & thanks for the feedback