Custom Function Need to add new parameter

[Negi1984]

Hi All,

I have a custom function mentioned below and want to add new parameter "Country" in it. Can any body help me out how to do that ?

Note : Country will be in Column "B" in SBD Price sheet.

Public Function Find_Price(ByVal Item As String) As Double

Set c = Sheets("SBD Price").Range("G:G").Find(Item)
If Not c Is Nothing Then
R = Sheets("SBD Price").Range("G:G").Find(Item).Row
Dim IWP_Cost, SBD_cost As Double
SBD_cost = Sheets("SBD Price").Cells(R, 9)
Find_Price = SBD_cost
End If
End Function

 

[Eric W]

Not sure why you need a custom function for that, it's just a VLOOKUP:

=IFERROR(VLOOKUP(E1,'SBD Price'!G:I,3,0),"")

where E1 contains your Item.

And if Item is unique within column G, you can also use:

=SUMIFS('SBD Price'!I:I,'SBD Price'!G:G,E1)

Which brings us to your question. You can add another parameter to that formula quite easily:

=SUMIFS('SBD Price'!I:I,'SBD Price'!G:G,E1,'SBD Price'!H:H,E2)

If your Country lookup column is H, and your lookup country is in E2, you can use the above formula. Adjust as needed.

Hope this helps!

 

[Negi1984]

Hi Eric,

If its possible to what changed required in above Macro code instead of formula ?
I am looking for customize Macro function.

Not sure why you need a custom function for that, it's just a VLOOKUP:

=IFERROR(VLOOKUP(E1,'SBD Price'!G:I,3,0),"")

where E1 contains your Item.

And if Item is unique within column G, you can also use:

=SUMIFS('SBD Price'!I:I,'SBD Price'!G:G,E1)

Which brings us to your question. You can add another parameter to that formula quite easily:

=SUMIFS('SBD Price'!I:I,'SBD Price'!G:G,E1,'SBD Price'!H:H,E2)

If your Country lookup column is H, and your lookup country is in E2, you can use the above formula. Adjust as needed.

Hope this helps!

 

[Eric W]

Public Function Find_Price(ByVal Item As String, ByVal Country As String) As Double
    Find_Price = Evaluate("SUMIFS('SBD Price'!I:I,'SBD Price'!G:G,""" & Item & """,'SBD Price'!H:H,""" & Country & """)")
End Function

This is still just evaluating a formula, but it's more efficient than executing a Find twice, more times actually, if we have to look for Country too.