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.

 

[THE_NEW_XL_GUY]

Let me know if I need to give any more details, am really stuck in this.. thanks in advance!

 

[Fluff]

Maybe

   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), [COLOR=#ff0000]tb1.Value[/COLOR])
   For Cnt = 1 To Qty
      Set Fnd = Rows(1).Find([COLOR=#ff0000]tb1.Value[/COLOR], Fnd, , xlWhole, , , False, , False)
      NewMax = Application.WorksheetFunction.Max(Fnd.EntireColumn)
      If NewMax > max_num Then max_num = NewMax
   Next Cnt

Where the values in red are the name of the textbox that holds your search value

 

[THE_NEW_XL_GUY]

thank you very much! that kinda made me understood to use when we have three columns with same names and

if we have single column with some name, does the below code work

Dim fNameAndPath As Variant, wb As Workbook, ws As Worksheet, rng2 As Range

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)
rng2 = Range("A1:E1").Find("name").End(xlDown) ' finds column name in a1 to e1 then takes found column complete rows
max_num3 = Application.WorksheetFunction.Max(rng2) ' check that complete row for max values and this max_num3 called in another button
wb.Close savechanges:=False.

correct me in this code kindly!

 

[Fluff]

The code I supplied will work for 1 or more columns, you don't need separate code.

 

[THE_NEW_XL_GUY]

code you gave worked without a problem. thanks a ton for that! but I want to know where problem lies in my code.. understanding that all. if you have time kindly help me know my error

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)
rng2 = Range("A1:E1").Find("name").End(xlDown) ==== finds column name in a1 to e1 then takes found column complete rows
max_num3 = Application.WorksheetFunction.Max(rng2)====check that complete row for max values and this max_num3 called in another button
wb.Close savechanges:=False.

 

[Fluff]

First off, rng2 is an object (in this case a range) & therefore needs to be Set in the same way as you did for wb.

Once you have corrected that, put a message box after that line like

MsgBox rng2.address

& see what that says

 

[THE_NEW_XL_GUY]

yea understood! thanks!

 

[THE_NEW_XL_GUY]

small query again, the code you gave is finding max value only in last column. ex: there are 10 columns with same names and its finding only 10th column max value.

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*")
For Cnt = 1 To Qty
Set Fnd = Rows(1).Find("* in", Fnd, , xlWhole, , , False, , False)
NewMax = Application.WorksheetFunction.Max(Fnd.EntireColumn)
If NewMax > max_num Then

'''

Am providing column character in code and there is no max_value before to be compared, I need to know max value among all those 10 columns. need help again mate

 

[THE_NEW_XL_GUY]

Need help on this please.
code:

Set Fnd = Range("A1")
   Qty = WorksheetFunction.CountIf(Rows(1), [COLOR=#ff0000]"* in*"[/COLOR])
   For Cnt = 1 To Qty
      Set Fnd = Rows(1).Find([COLOR=#ff0000]"* in*"[/COLOR], Fnd, , xlWhole, , , False, , False)
      NewMax = Application.WorksheetFunction.Max(Fnd.EntireColumn)
      If NewMax > max_num Then