Using named ranges in vba function

[ndblasa]

Hello,

I am trying to use named ranges in a VBA function but I am getting a "Compile error: Sub or Function not defined". What I am trying to do is a countifs function:

a = Range("K1")
b = Range("A:A") - lookup range for a
c = Range("L1")
d = Range("B:B") - lookup range for c
e = Range ("M1")
f = Range("C:C") - lookup range for e

d = CountIfs (a, b, c, d, e, f)

Any ideas why this is not working?

Thanks for the help!

 

[VBA Geek]

Hello - First, you need to declare a,b,c,d,e,f as Range and use:

SET a = Range("K1")
SET b = Range("A:A") - lookup range for a
SET c = Range("L1")
SET d = Range("B:B") - lookup range for c
SET e = Range ("M1")
SET f = Range("C:C") - lookup range for e

second, you need to use another variable name for the result of the countifs since D is already being used for the B:B range, and declare it as long

third, you need to add Worksheetfunction before the countifs MyResult = worksheetfunction.countIfs(a,b,c,d,e,f)

 

[ndblasa]

Worked perfectly! thanks a lot

Hello - First, you need to declare a,b,c,d,e,f as Range and use:

SET a = Range("K1")
SET b = Range("A:A") - lookup range for a
SET c = Range("L1")
SET d = Range("B:B") - lookup range for c
SET e = Range ("M1")
SET f = Range("C:C") - lookup range for e

second, you need to use another variable name for the result of the countifs since D is already being used for the B:B range, and declare it as long

third, you need to add Worksheetfunction before the countifs MyResult = worksheetfunction.countIfs(a,b,c,d,e,f)

 

[ndblasa]

I am trying to use the match function now instead of countifs but I am still getting an error:

Dim origin As Range, origin_range As Range, action_type As Range, action_type_range As Range, day_of_month As Range, day_of_month_range As Range
Dim myresult As Long

Set origin = Range("F2")
Set origin_range = Range("A:A")
Set action_type = Range("G2")
Set action_type_range = Range("B:B")
Set day_of_month = Range("H2")
Set day_of_month_range = Range("C:C")

myresult = WorksheetFunction.Match(origin & action_type & day_of_month, origin_range & action_type_range & day_of_month_range, 0)

MsgBox myresult


Any ideas?
Thanks!