Find Max value in Macro Function

[Shanmugam N]

ES2001 500
ES2002 300
ES2002 200
ES2003 800
ES2002 200

ESMaxIF (ES2002) - =ESMaxIF(B3:B7,C11,C3:C7)

Hi,

I want to find Maximum value of (ES2002) using above formula in VBA Macro function
Could you please help me on this...

 

[Hui]

Shanmugam

Firstly, Welcome to the Chandoo.org Forums

Why not use a Spreadsheet Formula :
=MAX(IF(B3:B7=C11,C3:C7,)) Ctrl+Shift+Enter

 

[Hui]

If you must use some VBA

Function ESMax(Crit As Range, Val As Range, MaxRng As Range) As Double
Dim c As Range
Dim myArr As Variant
Dim tmp As Double, i As Integer

myArr = MaxRng

i = 1
For Each c In Crit
  If c.Value = Val.Value Then
  If myArr(i, 1) > tmp Then tmp = myArr(i, 1)
  End If
  i = i + 1
Next

ESMax = tmp

End Function

 

[Shanmugam N]

First thank you very much for the prompt response
Yes we can but I am learning macro just wanted this for my learning purposes.

 

[Shanmugam N]

First thank you very much for the prompt response
Yes we can but I am learning macro just wanted this for my learning purposes.

 

[Shanmugam N]

Hi,

One more doubt on this if it is minimum value to be find means what changes will be there in this code.

 

[Hui]

Function ESMin(Crit As Range, Val As Range, MinRng As Range) As Double
Dim c As Range
Dim myArr As Variant
Dim tmp As Double, i As Integer

myArr = MinRng

i = 1
For Each c In Crit
  If c.Value = Val.Value Then
  If tmp = 0 Then tmp = myArr(i, 1)
  If myArr(i, 1) < tmp Then tmp = myArr(i, 1)
  End If
  i = i + 1
Next

ESMin = tmp

End Function

 

[Shanmugam N]

Hi i need a macro coding for the below question i tried something but its not working could you pls help..?

Create a macro which will ask the user how many workbooks to be created and save that many workbooks. The title can be saved as 1.xlsx , 2.xlsx and so on

I tried like this
Sub Scenario15()
Dim i As Double
For i = 1 To Workbooks.Count
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Shan" & i & ".xlsx"
ActiveWorkbook.Close
Next
End Sub

 

[Hui]

Shanmugam

Please start a new Thread as this post has no relationship to the Existing post

Hui...

Hi i need a macro coding for the below question i tried something but its not working could you pls help..?

Create a macro which will ask the user how many workbooks to be created and save that many workbooks. The title can be saved as 1.xlsx , 2.xlsx and so on

I tried like this
Sub Scenario15()
Dim i As Double
For i = 1 To Workbooks.Count
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Shan" & i & ".xlsx"
ActiveWorkbook.Close
Next
End Sub