How do you return the range of the active cell in a messagebox? (i.e. if A1
is selected it returns A1)

MsgBox ActiveCell.Address

Mangesh



"Andibevan" wrote in message
...
How do you return the range of the active cell in a messagebox? (i.e. if
A1
is selected it returns A1)

Chears Mangesh - That was proving very difficult to find.

"Mangesh Yadav" wrote in message
...
MsgBox ActiveCell.Address

Mangesh



"Andibevan" wrote in message
...
How do you return the range of the active cell in a messagebox? (i.e. if
A1
is selected it returns A1)

or MsgBox Activecell.Address(False,False) to get A1 style.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mangesh Yadav" wrote in message
...
MsgBox ActiveCell.Address

Mangesh



"Andibevan" wrote in message
...
How do you return the range of the active cell in a messagebox? (i.e. if
A1
is selected it returns A1)

well, it does happen to me too sometimes :)

Mangesh



"Andibevan" wrote in message
...
Chears Mangesh - That was proving very difficult to find.

"Mangesh Yadav" wrote in message
...
MsgBox ActiveCell.Address

Mangesh



"Andibevan" wrote in message
...
How do you return the range of the active cell in a messagebox? (i.e.
if
A1
is selected it returns A1)

Thanks Bob - I was just about to ask exactly that as my app needs non-an
absolute reference

"Bob Phillips" wrote in message
...
or MsgBox Activecell.Address(False,False) to get A1 style.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mangesh Yadav" wrote in message
...
MsgBox ActiveCell.Address

Mangesh



"Andibevan" wrote in message
...
How do you return the range of the active cell in a messagebox? (i.e.
if
A1
is selected it returns A1)

Have just had a closer look and I am off the mark for what I want to do.

Is it possible for a UDF to return the address or column of the cell which
it is in. This isn't actually what I need to do but I can manage all the
other parts.

Essentially I have a huge SUMPRODUCT formula which uses multiple If
statement and I would find it much easier to put all the coding in a UDF.
If the UDF new which cell it was running in, I would not have any need for
any locations for variables to be included.

Thanks

Andi

"Andibevan" wrote in message
...
Thanks Bob - I was just about to ask exactly that as my app needs non-an
absolute reference

"Bob Phillips" wrote in message
...
or MsgBox Activecell.Address(False,False) to get A1 style.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mangesh Yadav" wrote in message
...
MsgBox ActiveCell.Address

Mangesh



"Andibevan" wrote in message
...
How do you return the range of the active cell in a messagebox?
(i.e.
if
A1
is selected it returns A1)

I am not absolutely sure what you want Andi, but a UDF would simply be

Function myAddress()
myAddress = Application.Caller.Address(False, False)
End Function

but this will only work as a worksheet function, so I can't quite see it's
value.

From within a function, you can get a cell's row and/or column number with

rng.Row (.Column)

where rng is any range object, such as Selection, Activecell, or
Range("A1").

Perhaps if you give a bit more detail I can be more helpful.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
Have just had a closer look and I am off the mark for what I want to do.

Is it possible for a UDF to return the address or column of the cell which
it is in. This isn't actually what I need to do but I can manage all the
other parts.

Essentially I have a huge SUMPRODUCT formula which uses multiple If
statement and I would find it much easier to put all the coding in a UDF.
If the UDF new which cell it was running in, I would not have any need for
any locations for variables to be included.

Thanks

Andi

"Andibevan" wrote in message
...
Thanks Bob - I was just about to ask exactly that as my app needs non-an
absolute reference

"Bob Phillips" wrote in message
...
or MsgBox Activecell.Address(False,False) to get A1 style.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mangesh Yadav" wrote in message
...
MsgBox ActiveCell.Address

Mangesh



"Andibevan" wrote in message
...
How do you return the range of the active cell in a messagebox?
(i.e.
if
A1
is selected it returns A1)

"Mangesh Yadav" wrote in message
...
well, it does happen to me too sometimes :)

.... sometimes, I wish it were only sometimes :-)

This may seem to be completely unrelated (probably due to my poor
explanation) but I am trying to create a UDF for the following formulae:-

=SUMPRODUCT((I2=Hol_Start)*(I2<=Hol_End)*(Hol_Nam e="Andi")*(Hol_Type_Code))

Hol_Start, Hol_End and Hol_Type_Code are all defined ranges on a data table

The reason I was persuing the path before is that cell I2 is always in a
relative position to the cell where the function was.

I am completely stuck on getting Sumproduct into a UDF, Particularly when
using named ranges. Thanks for your answer - that knowledge will come in
handy for some other things as well.



"Bob Phillips" wrote in message
...
I am not absolutely sure what you want Andi, but a UDF would simply be

Function myAddress()
myAddress = Application.Caller.Address(False, False)
End Function

but this will only work as a worksheet function, so I can't quite see it's
value.

From within a function, you can get a cell's row and/or column number with

rng.Row (.Column)

where rng is any range object, such as Selection, Activecell, or
Range("A1").

Perhaps if you give a bit more detail I can be more helpful.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
Have just had a closer look and I am off the mark for what I want to do.

Is it possible for a UDF to return the address or column of the cell
which
it is in. This isn't actually what I need to do but I can manage all
the
other parts.

Essentially I have a huge SUMPRODUCT formula which uses multiple If
statement and I would find it much easier to put all the coding in a
UDF.
If the UDF new which cell it was running in, I would not have any need
for
any locations for variables to be included.

Thanks

Andi

"Andibevan" wrote in message
...
Thanks Bob - I was just about to ask exactly that as my app needs
non-an
absolute reference

"Bob Phillips" wrote in message
...
or MsgBox Activecell.Address(False,False) to get A1 style.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mangesh Yadav" wrote in message
...
MsgBox ActiveCell.Address

Mangesh



"Andibevan" wrote in message
...
How do you return the range of the active cell in a messagebox?
(i.e.
if
A1
is selected it returns A1)

Andi,

That is a relatively simple SP formula, I wouldn't have thought necessary to
put in a UDF.

By declarintg the cell, I2, in relative terme, when you copy to another
cell, it will update accordingly, which seems to be what you wanted to do.
However, you also seem to test one cell against one cell (I2=Hol_Start),
and then many cells against one value (Hol_Name="Andi") which is a no-no
with SP

So I don't get it yet, but to answer your question, here is a simple example

Function GetValue(val1, rng1 As Range, val2, rng2 As Range)
Dim sFormula As String
sFormula = "SUMPRODUCT(--("
If TypeName(val1) = "String" Then
sFormula = sFormula & """" & val1 & """"
Else
sFormula = sFormula & val1
End If
sFormula = sFormula & "=" & rng1.Address & "),--("
If TypeName(val2) = "String" Then
sFormula = sFormula & """" & val2
Else
sFormula = sFormula & val2
End If
sFormula = sFormula & "=" & rng2.Address & "))"
GetValue = Evaluate(sFormula)
End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
This may seem to be completely unrelated (probably due to my poor
explanation) but I am trying to create a UDF for the following formulae:-


=SUMPRODUCT((I2=Hol_Start)*(I2<=Hol_End)*(Hol_Nam e="Andi")*(Hol_Type_Code))

Hol_Start, Hol_End and Hol_Type_Code are all defined ranges on a data
table

The reason I was persuing the path before is that cell I2 is always in a
relative position to the cell where the function was.

I am completely stuck on getting Sumproduct into a UDF, Particularly when
using named ranges. Thanks for your answer - that knowledge will come in
handy for some other things as well.



"Bob Phillips" wrote in message
...
I am not absolutely sure what you want Andi, but a UDF would simply be

Function myAddress()
myAddress = Application.Caller.Address(False, False)
End Function

but this will only work as a worksheet function, so I can't quite see
it's
value.

From within a function, you can get a cell's row and/or column number
with

rng.Row (.Column)

where rng is any range object, such as Selection, Activecell, or
Range("A1").

Perhaps if you give a bit more detail I can be more helpful.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
Have just had a closer look and I am off the mark for what I want to
do.

Is it possible for a UDF to return the address or column of the cell
which
it is in. This isn't actually what I need to do but I can manage all
the
other parts.

Essentially I have a huge SUMPRODUCT formula which uses multiple If
statement and I would find it much easier to put all the coding in a
UDF.
If the UDF new which cell it was running in, I would not have any need
for
any locations for variables to be included.

Thanks

Andi

"Andibevan" wrote in message
...
Thanks Bob - I was just about to ask exactly that as my app needs
non-an
absolute reference

"Bob Phillips" wrote in message
...
or MsgBox Activecell.Address(False,False) to get A1 style.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mangesh Yadav" wrote in message
...
MsgBox ActiveCell.Address

Mangesh



"Andibevan" wrote in message
...
How do you return the range of the active cell in a
messagebox?
(i.e.
if
A1
is selected it returns A1)

Bob,

Sorry - I seem to be doing really badly at explaining things.

The reason I want to create a UDF is (1) Stop Users from messing up the
formulas (2) Make the 1000s of cells that use this formula easier to develop
and modify for future requirements as it removes the requirement to copy and
past the formula (quite often I am asked to modify sheets on the fly in
meetings and the ability to make changes in 30 seconds rather than 2 minutes
can invaluable)

Hol_End,Hol_Start,Hol_Name and Hol_Type_Code are all named ranges of the
same size.

They are in a table that has Names, Holiday Start Dates, Holiday finish
dates and Holiday type (Vacation, public holiday, etc). Each resource may
have multiple entries but obviously none will overlap. I think it may be
more sensible to merely convert the sumproduct formulae to a udf and have 2
inputs.

To keep it simple, how do I convert :- (Hol_Name=A2)*(Hol_Type_Code=1)) to a
UDF where there is only 1 input (a2)?

Thanks in advance.



"Bob Phillips" wrote in message
...
Andi,

That is a relatively simple SP formula, I wouldn't have thought necessary
to
put in a UDF.

By declarintg the cell, I2, in relative terme, when you copy to another
cell, it will update accordingly, which seems to be what you wanted to do.
However, you also seem to test one cell against one cell (I2=Hol_Start),
and then many cells against one value (Hol_Name="Andi") which is a no-no
with SP

So I don't get it yet, but to answer your question, here is a simple
example

Function GetValue(val1, rng1 As Range, val2, rng2 As Range)
Dim sFormula As String
sFormula = "SUMPRODUCT(--("
If TypeName(val1) = "String" Then
sFormula = sFormula & """" & val1 & """"
Else
sFormula = sFormula & val1
End If
sFormula = sFormula & "=" & rng1.Address & "),--("
If TypeName(val2) = "String" Then
sFormula = sFormula & """" & val2
Else
sFormula = sFormula & val2
End If
sFormula = sFormula & "=" & rng2.Address & "))"
GetValue = Evaluate(sFormula)
End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
This may seem to be completely unrelated (probably due to my poor
explanation) but I am trying to create a UDF for the following
formulae:-



=SUMPRODUCT((I2=Hol_Start)*(I2<=Hol_End)*(Hol_Nam e="Andi")*(Hol_Type_Code))

Hol_Start, Hol_End and Hol_Type_Code are all defined ranges on a data
table

The reason I was persuing the path before is that cell I2 is always in a
relative position to the cell where the function was.

I am completely stuck on getting Sumproduct into a UDF, Particularly
when
using named ranges. Thanks for your answer - that knowledge will come
in
handy for some other things as well.



"Bob Phillips" wrote in message
...
I am not absolutely sure what you want Andi, but a UDF would simply be

Function myAddress()
myAddress = Application.Caller.Address(False, False)
End Function

but this will only work as a worksheet function, so I can't quite see
it's
value.

From within a function, you can get a cell's row and/or column number
with

rng.Row (.Column)

where rng is any range object, such as Selection, Activecell, or
Range("A1").

Perhaps if you give a bit more detail I can be more helpful.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
Have just had a closer look and I am off the mark for what I want to
do.

Is it possible for a UDF to return the address or column of the cell
which
it is in. This isn't actually what I need to do but I can manage
all
the
other parts.

Essentially I have a huge SUMPRODUCT formula which uses multiple If
statement and I would find it much easier to put all the coding in a
UDF.
If the UDF new which cell it was running in, I would not have any
need
for
any locations for variables to be included.

Thanks

Andi

"Andibevan" wrote in message
...
Thanks Bob - I was just about to ask exactly that as my app needs
non-an
absolute reference

"Bob Phillips" wrote in
message
...
or MsgBox Activecell.Address(False,False) to get A1 style.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mangesh Yadav" wrote in message
...
MsgBox ActiveCell.Address

Mangesh



"Andibevan" wrote in message
...
How do you return the range of the active cell in a
messagebox?
(i.e.
if
A1
is selected it returns A1)

I think I may now have worked out how to do the sumproduct but I am having a
problem with a named range:-

If I try:-

HolAvail = CountA(Hol_Name) - It says this is not a defined range
If I use Evaluate("Counta(Hol_Name)")) - it gives me a value?

Any Ideas why? I think once I sort this bit my UDF will work

"Andibevan" wrote in message
...
Bob,

Sorry - I seem to be doing really badly at explaining things.

The reason I want to create a UDF is (1) Stop Users from messing up the
formulas (2) Make the 1000s of cells that use this formula easier to
develop
and modify for future requirements as it removes the requirement to copy
and
past the formula (quite often I am asked to modify sheets on the fly in
meetings and the ability to make changes in 30 seconds rather than 2
minutes
can invaluable)

Hol_End,Hol_Start,Hol_Name and Hol_Type_Code are all named ranges of the
same size.

They are in a table that has Names, Holiday Start Dates, Holiday finish
dates and Holiday type (Vacation, public holiday, etc). Each resource may
have multiple entries but obviously none will overlap. I think it may be
more sensible to merely convert the sumproduct formulae to a udf and have
2
inputs.

To keep it simple, how do I convert :- (Hol_Name=A2)*(Hol_Type_Code=1)) to
a
UDF where there is only 1 input (a2)?

Thanks in advance.



"Bob Phillips" wrote in message
...
Andi,

That is a relatively simple SP formula, I wouldn't have thought
necessary
to
put in a UDF.

By declarintg the cell, I2, in relative terme, when you copy to another
cell, it will update accordingly, which seems to be what you wanted to
do.
However, you also seem to test one cell against one cell
(I2=Hol_Start),
and then many cells against one value (Hol_Name="Andi") which is a no-no
with SP

So I don't get it yet, but to answer your question, here is a simple
example

Function GetValue(val1, rng1 As Range, val2, rng2 As Range)
Dim sFormula As String
sFormula = "SUMPRODUCT(--("
If TypeName(val1) = "String" Then
sFormula = sFormula & """" & val1 & """"
Else
sFormula = sFormula & val1
End If
sFormula = sFormula & "=" & rng1.Address & "),--("
If TypeName(val2) = "String" Then
sFormula = sFormula & """" & val2
Else
sFormula = sFormula & val2
End If
sFormula = sFormula & "=" & rng2.Address & "))"
GetValue = Evaluate(sFormula)
End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
This may seem to be completely unrelated (probably due to my poor
explanation) but I am trying to create a UDF for the following
formulae:-




=SUMPRODUCT((I2=Hol_Start)*(I2<=Hol_End)*(Hol_Nam e="Andi")*(Hol_Type_Code))

Hol_Start, Hol_End and Hol_Type_Code are all defined ranges on a data
table

The reason I was persuing the path before is that cell I2 is always in
a
relative position to the cell where the function was.

I am completely stuck on getting Sumproduct into a UDF, Particularly
when
using named ranges. Thanks for your answer - that knowledge will come
in
handy for some other things as well.



"Bob Phillips" wrote in message
...
I am not absolutely sure what you want Andi, but a UDF would simply
be

Function myAddress()
myAddress = Application.Caller.Address(False, False)
End Function

but this will only work as a worksheet function, so I can't quite
see
it's
value.

From within a function, you can get a cell's row and/or column
number
with

rng.Row (.Column)

where rng is any range object, such as Selection, Activecell, or
Range("A1").

Perhaps if you give a bit more detail I can be more helpful.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
Have just had a closer look and I am off the mark for what I want
to
do.

Is it possible for a UDF to return the address or column of the
cell
which
it is in. This isn't actually what I need to do but I can manage
all
the
other parts.

Essentially I have a huge SUMPRODUCT formula which uses multiple
If
statement and I would find it much easier to put all the coding in
a
UDF.
If the UDF new which cell it was running in, I would not have any
need
for
any locations for variables to be included.

Thanks

Andi

"Andibevan" wrote in message
...
Thanks Bob - I was just about to ask exactly that as my app
needs
non-an
absolute reference

"Bob Phillips" wrote in
message
...
or MsgBox Activecell.Address(False,False) to get A1 style.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mangesh Yadav" wrote in
message
...
MsgBox ActiveCell.Address

Mangesh



"Andibevan" wrote in
message
...
How do you return the range of the active cell in a
messagebox?
(i.e.
if
A1
is selected it returns A1)

I think I will re-ask this question as I have got rather away from the
original thread here.

"Andibevan" wrote in message
...
I think I may now have worked out how to do the sumproduct but I am having
a
problem with a named range:-

If I try:-

HolAvail = CountA(Hol_Name) - It says this is not a defined range
If I use Evaluate("Counta(Hol_Name)")) - it gives me a value?

Any Ideas why? I think once I sort this bit my UDF will work

"Andibevan" wrote in message
...
Bob,

Sorry - I seem to be doing really badly at explaining things.

The reason I want to create a UDF is (1) Stop Users from messing up the
formulas (2) Make the 1000s of cells that use this formula easier to
develop
and modify for future requirements as it removes the requirement to copy
and
past the formula (quite often I am asked to modify sheets on the fly in
meetings and the ability to make changes in 30 seconds rather than 2
minutes
can invaluable)

Hol_End,Hol_Start,Hol_Name and Hol_Type_Code are all named ranges of the
same size.

They are in a table that has Names, Holiday Start Dates, Holiday finish
dates and Holiday type (Vacation, public holiday, etc). Each resource
may
have multiple entries but obviously none will overlap. I think it may
be
more sensible to merely convert the sumproduct formulae to a udf and
have
2
inputs.

To keep it simple, how do I convert :- (Hol_Name=A2)*(Hol_Type_Code=1))
to
a
UDF where there is only 1 input (a2)?

Thanks in advance.



"Bob Phillips" wrote in message
...
Andi,

That is a relatively simple SP formula, I wouldn't have thought
necessary
to
put in a UDF.

By declarintg the cell, I2, in relative terme, when you copy to
another
cell, it will update accordingly, which seems to be what you wanted to
do.
However, you also seem to test one cell against one cell
(I2=Hol_Start),
and then many cells against one value (Hol_Name="Andi") which is a
no-no
with SP

So I don't get it yet, but to answer your question, here is a simple
example

Function GetValue(val1, rng1 As Range, val2, rng2 As Range)
Dim sFormula As String
sFormula = "SUMPRODUCT(--("
If TypeName(val1) = "String" Then
sFormula = sFormula & """" & val1 & """"
Else
sFormula = sFormula & val1
End If
sFormula = sFormula & "=" & rng1.Address & "),--("
If TypeName(val2) = "String" Then
sFormula = sFormula & """" & val2
Else
sFormula = sFormula & val2
End If
sFormula = sFormula & "=" & rng2.Address & "))"
GetValue = Evaluate(sFormula)
End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
This may seem to be completely unrelated (probably due to my poor
explanation) but I am trying to create a UDF for the following
formulae:-





=SUMPRODUCT((I2=Hol_Start)*(I2<=Hol_End)*(Hol_Nam e="Andi")*(Hol_Type_Code))

Hol_Start, Hol_End and Hol_Type_Code are all defined ranges on a
data
table

The reason I was persuing the path before is that cell I2 is always
in
a
relative position to the cell where the function was.

I am completely stuck on getting Sumproduct into a UDF, Particularly
when
using named ranges. Thanks for your answer - that knowledge will
come
in
handy for some other things as well.



"Bob Phillips" wrote in message
...
I am not absolutely sure what you want Andi, but a UDF would
simply
be

Function myAddress()
myAddress = Application.Caller.Address(False, False)
End Function

but this will only work as a worksheet function, so I can't quite
see
it's
value.

From within a function, you can get a cell's row and/or column
number
with

rng.Row (.Column)

where rng is any range object, such as Selection, Activecell, or
Range("A1").

Perhaps if you give a bit more detail I can be more helpful.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
Have just had a closer look and I am off the mark for what I
want
to
do.

Is it possible for a UDF to return the address or column of the
cell
which
it is in. This isn't actually what I need to do but I can
manage
all
the
other parts.

Essentially I have a huge SUMPRODUCT formula which uses multiple
If
statement and I would find it much easier to put all the coding
in
a
UDF.
If the UDF new which cell it was running in, I would not have
any
need
for
any locations for variables to be included.

Thanks

Andi

"Andibevan" wrote in message
...
Thanks Bob - I was just about to ask exactly that as my app
needs
non-an
absolute reference

"Bob Phillips" wrote in
message
...
or MsgBox Activecell.Address(False,False) to get A1 style.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mangesh Yadav" wrote in
message
...
MsgBox ActiveCell.Address

Mangesh



"Andibevan" wrote in
message
...
How do you return the range of the active cell in a
messagebox?
(i.e.
if
A1
is selected it returns A1)

I'll keep trying to help Andi even though I still don't fully get it, but
that is irrelevant really <vbg

HolAvail = CountA(Hol_Name)

If HolAvail is a variable in your VBA, but Hol_Name is a named range then I
think this will help you

HolAvail = Application.COUNTA(Range("Hol_Name"))

The evaluate works because it is converting an Excel name, so it is the same
as if you were doing it in Excel. VBA doesn't, you have to tell VBA what
objects you are using. In actuality, in your formula you could do what you
showed, namely using COUNTA(HolAvail).

Regards

Bob

"Andibevan" wrote in message
...
I think I may now have worked out how to do the sumproduct but I am having
a
problem with a named range:-

If I try:-

HolAvail = CountA(Hol_Name) - It says this is not a defined range
If I use Evaluate("Counta(Hol_Name)")) - it gives me a value?

Any Ideas why? I think once I sort this bit my UDF will work

"Andibevan" wrote in message
...
Bob,

Sorry - I seem to be doing really badly at explaining things.

The reason I want to create a UDF is (1) Stop Users from messing up the
formulas (2) Make the 1000s of cells that use this formula easier to
develop
and modify for future requirements as it removes the requirement to copy
and
past the formula (quite often I am asked to modify sheets on the fly in
meetings and the ability to make changes in 30 seconds rather than 2
minutes
can invaluable)

Hol_End,Hol_Start,Hol_Name and Hol_Type_Code are all named ranges of the
same size.

They are in a table that has Names, Holiday Start Dates, Holiday finish
dates and Holiday type (Vacation, public holiday, etc). Each resource
may
have multiple entries but obviously none will overlap. I think it may
be
more sensible to merely convert the sumproduct formulae to a udf and
have
2
inputs.

To keep it simple, how do I convert :- (Hol_Name=A2)*(Hol_Type_Code=1))
to
a
UDF where there is only 1 input (a2)?

Thanks in advance.



"Bob Phillips" wrote in message
...
Andi,

That is a relatively simple SP formula, I wouldn't have thought
necessary
to
put in a UDF.

By declarintg the cell, I2, in relative terme, when you copy to
another
cell, it will update accordingly, which seems to be what you wanted to
do.
However, you also seem to test one cell against one cell
(I2=Hol_Start),
and then many cells against one value (Hol_Name="Andi") which is a
no-no
with SP

So I don't get it yet, but to answer your question, here is a simple
example

Function GetValue(val1, rng1 As Range, val2, rng2 As Range)
Dim sFormula As String
sFormula = "SUMPRODUCT(--("
If TypeName(val1) = "String" Then
sFormula = sFormula & """" & val1 & """"
Else
sFormula = sFormula & val1
End If
sFormula = sFormula & "=" & rng1.Address & "),--("
If TypeName(val2) = "String" Then
sFormula = sFormula & """" & val2
Else
sFormula = sFormula & val2
End If
sFormula = sFormula & "=" & rng2.Address & "))"
GetValue = Evaluate(sFormula)
End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
This may seem to be completely unrelated (probably due to my poor
explanation) but I am trying to create a UDF for the following
formulae:-





=SUMPRODUCT((I2=Hol_Start)*(I2<=Hol_End)*(Hol_Nam e="Andi")*(Hol_Type_Code))

Hol_Start, Hol_End and Hol_Type_Code are all defined ranges on a
data
table

The reason I was persuing the path before is that cell I2 is always
in
a
relative position to the cell where the function was.

I am completely stuck on getting Sumproduct into a UDF, Particularly
when
using named ranges. Thanks for your answer - that knowledge will
come
in
handy for some other things as well.



"Bob Phillips" wrote in message
...
I am not absolutely sure what you want Andi, but a UDF would
simply
be

Function myAddress()
myAddress = Application.Caller.Address(False, False)
End Function

but this will only work as a worksheet function, so I can't quite
see
it's
value.

From within a function, you can get a cell's row and/or column
number
with

rng.Row (.Column)

where rng is any range object, such as Selection, Activecell, or
Range("A1").

Perhaps if you give a bit more detail I can be more helpful.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
Have just had a closer look and I am off the mark for what I
want
to
do.

Is it possible for a UDF to return the address or column of the
cell
which
it is in. This isn't actually what I need to do but I can
manage
all
the
other parts.

Essentially I have a huge SUMPRODUCT formula which uses multiple
If
statement and I would find it much easier to put all the coding
in
a
UDF.
If the UDF new which cell it was running in, I would not have
any
need
for
any locations for variables to be included.

Thanks

Andi

"Andibevan" wrote in message
...
Thanks Bob - I was just about to ask exactly that as my app
needs
non-an
absolute reference

"Bob Phillips" wrote in
message
...
or MsgBox Activecell.Address(False,False) to get A1 style.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mangesh Yadav" wrote in
message
...
MsgBox ActiveCell.Address

Mangesh



"Andibevan" wrote in
message
...
How do you return the range of the active cell in a
messagebox?
(i.e.
if
A1
is selected it returns A1)

Thanks Bob - much appreciated, I don't think I quite get it either :-), As
this is the Sumproduct I am trying to convert to a UDF:-

=SUMPRODUCT((IG$2=Hol_Start)*(IG$2<=Hol_End)*(Hol _Name=$A20)*(Hol_Type_Code))

I think the problems I am having are with declaring the named ranges as
objects within my VBA code.

How would I declare the named ranges Hol_Name, Hol_Type_Code, Hol_Start and
Hol_End within my VBA program so that they could be used in a vba sumproduct
formula?
This is the past list of the named ranges.
Hol_End =Sheet2!$C$2:$C$24
Hol_Name =Sheet2!$A$2:$A$24
Hol_Start =Sheet2!$B$2:$B$24
Hol_Type =Sheet2!$D$2:$D$24
Hol_Type_Code =Sheet2!$E$2:$E$24

Hope this makes it a bit clearer.

Ta

Andi


"Bob Phillips" wrote in message
...
I'll keep trying to help Andi even though I still don't fully get it, but
that is irrelevant really <vbg

HolAvail = CountA(Hol_Name)

If HolAvail is a variable in your VBA, but Hol_Name is a named range then
I
think this will help you

HolAvail = Application.COUNTA(Range("Hol_Name"))

The evaluate works because it is converting an Excel name, so it is the
same
as if you were doing it in Excel. VBA doesn't, you have to tell VBA what
objects you are using. In actuality, in your formula you could do what you
showed, namely using COUNTA(HolAvail).

Regards

Bob

"Andibevan" wrote in message
...
I think I may now have worked out how to do the sumproduct but I am
having
a
problem with a named range:-

If I try:-

HolAvail = CountA(Hol_Name) - It says this is not a defined range
If I use Evaluate("Counta(Hol_Name)")) - it gives me a value?

Any Ideas why? I think once I sort this bit my UDF will work

"Andibevan" wrote in message
...
Bob,

Sorry - I seem to be doing really badly at explaining things.

The reason I want to create a UDF is (1) Stop Users from messing up the
formulas (2) Make the 1000s of cells that use this formula easier to
develop
and modify for future requirements as it removes the requirement to
copy
and
past the formula (quite often I am asked to modify sheets on the fly in
meetings and the ability to make changes in 30 seconds rather than 2
minutes
can invaluable)

Hol_End,Hol_Start,Hol_Name and Hol_Type_Code are all named ranges of
the
same size.

They are in a table that has Names, Holiday Start Dates, Holiday finish
dates and Holiday type (Vacation, public holiday, etc). Each resource
may
have multiple entries but obviously none will overlap. I think it may
be
more sensible to merely convert the sumproduct formulae to a udf and
have
2
inputs.

To keep it simple, how do I convert :- (Hol_Name=A2)*(Hol_Type_Code=1))
to
a
UDF where there is only 1 input (a2)?

Thanks in advance.



"Bob Phillips" wrote in message
...
Andi,

That is a relatively simple SP formula, I wouldn't have thought
necessary
to
put in a UDF.

By declarintg the cell, I2, in relative terme, when you copy to
another
cell, it will update accordingly, which seems to be what you wanted
to
do.
However, you also seem to test one cell against one cell
(I2=Hol_Start),
and then many cells against one value (Hol_Name="Andi") which is a
no-no
with SP

So I don't get it yet, but to answer your question, here is a simple
example

Function GetValue(val1, rng1 As Range, val2, rng2 As Range)
Dim sFormula As String
sFormula = "SUMPRODUCT(--("
If TypeName(val1) = "String" Then
sFormula = sFormula & """" & val1 & """"
Else
sFormula = sFormula & val1
End If
sFormula = sFormula & "=" & rng1.Address & "),--("
If TypeName(val2) = "String" Then
sFormula = sFormula & """" & val2
Else
sFormula = sFormula & val2
End If
sFormula = sFormula & "=" & rng2.Address & "))"
GetValue = Evaluate(sFormula)
End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
This may seem to be completely unrelated (probably due to my poor
explanation) but I am trying to create a UDF for the following
formulae:-





=SUMPRODUCT((I2=Hol_Start)*(I2<=Hol_End)*(Hol_Nam e="Andi")*(Hol_Type_Code))

Hol_Start, Hol_End and Hol_Type_Code are all defined ranges on a
data
table

The reason I was persuing the path before is that cell I2 is always
in
a
relative position to the cell where the function was.

I am completely stuck on getting Sumproduct into a UDF,
Particularly
when
using named ranges. Thanks for your answer - that knowledge will
come
in
handy for some other things as well.



"Bob Phillips" wrote in message
...
I am not absolutely sure what you want Andi, but a UDF would
simply
be

Function myAddress()
myAddress = Application.Caller.Address(False, False)
End Function

but this will only work as a worksheet function, so I can't quite
see
it's
value.

From within a function, you can get a cell's row and/or column
number
with

rng.Row (.Column)

where rng is any range object, such as Selection, Activecell, or
Range("A1").

Perhaps if you give a bit more detail I can be more helpful.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
Have just had a closer look and I am off the mark for what I
want
to
do.

Is it possible for a UDF to return the address or column of the
cell
which
it is in. This isn't actually what I need to do but I can
manage
all
the
other parts.

Essentially I have a huge SUMPRODUCT formula which uses
multiple
If
statement and I would find it much easier to put all the coding
in
a
UDF.
If the UDF new which cell it was running in, I would not have
any
need
for
any locations for variables to be included.

Thanks

Andi

"Andibevan" wrote in message
...
Thanks Bob - I was just about to ask exactly that as my app
needs
non-an
absolute reference

"Bob Phillips" wrote in
message
...
or MsgBox Activecell.Address(False,False) to get A1 style.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mangesh Yadav" wrote in
message
...
MsgBox ActiveCell.Address

Mangesh



"Andibevan" wrote in
message
...
How do you return the range of the active cell in a
messagebox?
(i.e.
if
A1
is selected it returns A1)

Andi,

If these are named ranges, defined within your spreadsheet, and you are
using Evaluate you can use it as is. The only variables seems to be IG2 and
A20, so you could pass these in the UDF like so

Function GetVal(rng1 As Range, rng2 As Range)
GetVal = Evaluate("SUMPRODUCT((" & rng1.Address & _
"=Hol_Start)*(" & rng1.Address & _
"<=Hol_End)*(Hol_Name=)*(" & rng2.Address & _
")*(Hol_Type_Code))")
End Function

Used like =GetVal(IG$2,$A20)

If you are trying to avoid any arguments in your UDF, it will depend where
you are when calling it, but assuming that in your example you are in IG20,
then maybe you can use

Function GetVal()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Cells(2, Activecell.Column)
Set rng2 = Range("A" & Activecell.Row)
GetVal = Evaluate("SUMPRODUCT((" & rng1.Address & _
"=Hol_Start)*(" & rng1.Address & _
"<=Hol_End)*(Hol_Name=)*(" & rng2.Address & _
")*(Hol_Type_Code))")
End Function

Used like =GetVal() from IG20

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
Thanks Bob - much appreciated, I don't think I quite get it either :-),
As
this is the Sumproduct I am trying to convert to a UDF:-


=SUMPRODUCT((IG$2=Hol_Start)*(IG$2<=Hol_End)*(Hol _Name=$A20)*(Hol_Type_Code
))

I think the problems I am having are with declaring the named ranges as
objects within my VBA code.

How would I declare the named ranges Hol_Name, Hol_Type_Code, Hol_Start
and
Hol_End within my VBA program so that they could be used in a vba
sumproduct
formula?
This is the past list of the named ranges.
Hol_End =Sheet2!$C$2:$C$24
Hol_Name =Sheet2!$A$2:$A$24
Hol_Start =Sheet2!$B$2:$B$24
Hol_Type =Sheet2!$D$2:$D$24
Hol_Type_Code =Sheet2!$E$2:$E$24

Hope this makes it a bit clearer.

Ta

Andi


"Bob Phillips" wrote in message
...
I'll keep trying to help Andi even though I still don't fully get it,
but
that is irrelevant really <vbg

HolAvail = CountA(Hol_Name)

If HolAvail is a variable in your VBA, but Hol_Name is a named range
then
I
think this will help you

HolAvail = Application.COUNTA(Range("Hol_Name"))

The evaluate works because it is converting an Excel name, so it is the
same
as if you were doing it in Excel. VBA doesn't, you have to tell VBA what
objects you are using. In actuality, in your formula you could do what
you
showed, namely using COUNTA(HolAvail).

Regards

Bob

"Andibevan" wrote in message
...
I think I may now have worked out how to do the sumproduct but I am
having
a
problem with a named range:-

If I try:-

HolAvail = CountA(Hol_Name) - It says this is not a defined range
If I use Evaluate("Counta(Hol_Name)")) - it gives me a value?

Any Ideas why? I think once I sort this bit my UDF will work

"Andibevan" wrote in message
...
Bob,

Sorry - I seem to be doing really badly at explaining things.

The reason I want to create a UDF is (1) Stop Users from messing up
the
formulas (2) Make the 1000s of cells that use this formula easier to
develop
and modify for future requirements as it removes the requirement to
copy
and
past the formula (quite often I am asked to modify sheets on the fly
in
meetings and the ability to make changes in 30 seconds rather than 2
minutes
can invaluable)

Hol_End,Hol_Start,Hol_Name and Hol_Type_Code are all named ranges of
the
same size.

They are in a table that has Names, Holiday Start Dates, Holiday
finish
dates and Holiday type (Vacation, public holiday, etc). Each
resource
may
have multiple entries but obviously none will overlap. I think it
may
be
more sensible to merely convert the sumproduct formulae to a udf and
have
2
inputs.

To keep it simple, how do I convert :-
(Hol_Name=A2)*(Hol_Type_Code=1))
to
a
UDF where there is only 1 input (a2)?

Thanks in advance.



"Bob Phillips" wrote in message
...
Andi,

That is a relatively simple SP formula, I wouldn't have thought
necessary
to
put in a UDF.

By declarintg the cell, I2, in relative terme, when you copy to
another
cell, it will update accordingly, which seems to be what you wanted
to
do.
However, you also seem to test one cell against one cell
(I2=Hol_Start),
and then many cells against one value (Hol_Name="Andi") which is a
no-no
with SP

So I don't get it yet, but to answer your question, here is a
simple
example

Function GetValue(val1, rng1 As Range, val2, rng2 As Range)
Dim sFormula As String
sFormula = "SUMPRODUCT(--("
If TypeName(val1) = "String" Then
sFormula = sFormula & """" & val1 & """"
Else
sFormula = sFormula & val1
End If
sFormula = sFormula & "=" & rng1.Address & "),--("
If TypeName(val2) = "String" Then
sFormula = sFormula & """" & val2
Else
sFormula = sFormula & val2
End If
sFormula = sFormula & "=" & rng2.Address & "))"
GetValue = Evaluate(sFormula)
End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
This may seem to be completely unrelated (probably due to my poor
explanation) but I am trying to create a UDF for the following
formulae:-






=SUMPRODUCT((I2=Hol_Start)*(I2<=Hol_End)*(Hol_Nam e="Andi")*(Hol_Type_Code))

Hol_Start, Hol_End and Hol_Type_Code are all defined ranges on a
data
table

The reason I was persuing the path before is that cell I2 is
always
in
a
relative position to the cell where the function was.

I am completely stuck on getting Sumproduct into a UDF,
Particularly
when
using named ranges. Thanks for your answer - that knowledge will
come
in
handy for some other things as well.



"Bob Phillips" wrote in
message
...
I am not absolutely sure what you want Andi, but a UDF would
simply
be

Function myAddress()
myAddress = Application.Caller.Address(False, False)
End Function

but this will only work as a worksheet function, so I can't
quite
see
it's
value.

From within a function, you can get a cell's row and/or column
number
with

rng.Row (.Column)

where rng is any range object, such as Selection, Activecell, o
r
Range("A1").

Perhaps if you give a bit more detail I can be more helpful.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
Have just had a closer look and I am off the mark for what I
want
to
do.

Is it possible for a UDF to return the address or column of
the
cell
which
it is in. This isn't actually what I need to do but I can
manage
all
the
other parts.

Essentially I have a huge SUMPRODUCT formula which uses
multiple
If
statement and I would find it much easier to put all the
coding
in
a
UDF.
If the UDF new which cell it was running in, I would not have
any
need
for
any locations for variables to be included.

Thanks

Andi

"Andibevan" wrote in message
...
Thanks Bob - I was just about to ask exactly that as my app
needs
non-an
absolute reference

"Bob Phillips" wrote in
message
...
or MsgBox Activecell.Address(False,False) to get A1
style.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mangesh Yadav" wrote in
message
...
MsgBox ActiveCell.Address

Mangesh



"Andibevan" wrote in
message
...
How do you return the range of the active cell in a
messagebox?
(i.e.
if
A1
is selected it returns A1)

Nice one.

I haven't tested it yet but I notice the difference between yours and my
code. I had tried to a couple of alternatives but not declared the variables
as a range (I had declared them as date / string)

As always, very much appreciated.

Chears Bob

"Bob Phillips" wrote in message
...
Andi,

If these are named ranges, defined within your spreadsheet, and you are
using Evaluate you can use it as is. The only variables seems to be IG2
and
A20, so you could pass these in the UDF like so

Function GetVal(rng1 As Range, rng2 As Range)
GetVal = Evaluate("SUMPRODUCT((" & rng1.Address & _
"=Hol_Start)*(" & rng1.Address & _
"<=Hol_End)*(Hol_Name=)*(" & rng2.Address & _
")*(Hol_Type_Code))")
End Function

Used like =GetVal(IG$2,$A20)

If you are trying to avoid any arguments in your UDF, it will depend where
you are when calling it, but assuming that in your example you are in
IG20,
then maybe you can use

Function GetVal()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Cells(2, Activecell.Column)
Set rng2 = Range("A" & Activecell.Row)
GetVal = Evaluate("SUMPRODUCT((" & rng1.Address & _
"=Hol_Start)*(" & rng1.Address & _
"<=Hol_End)*(Hol_Name=)*(" & rng2.Address & _
")*(Hol_Type_Code))")
End Function

Used like =GetVal() from IG20

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
Thanks Bob - much appreciated, I don't think I quite get it either :-),
As
this is the Sumproduct I am trying to convert to a UDF:-


=SUMPRODUCT((IG$2=Hol_Start)*(IG$2<=Hol_End)*(Hol _Name=$A20)*(Hol_Type_Code
))

I think the problems I am having are with declaring the named ranges as
objects within my VBA code.

How would I declare the named ranges Hol_Name, Hol_Type_Code, Hol_Start
and
Hol_End within my VBA program so that they could be used in a vba
sumproduct
formula?
This is the past list of the named ranges.
Hol_End =Sheet2!$C$2:$C$24
Hol_Name =Sheet2!$A$2:$A$24
Hol_Start =Sheet2!$B$2:$B$24
Hol_Type =Sheet2!$D$2:$D$24
Hol_Type_Code =Sheet2!$E$2:$E$24

Hope this makes it a bit clearer.

Ta

Andi


"Bob Phillips" wrote in message
...
I'll keep trying to help Andi even though I still don't fully get it,
but
that is irrelevant really <vbg

HolAvail = CountA(Hol_Name)

If HolAvail is a variable in your VBA, but Hol_Name is a named range
then
I
think this will help you

HolAvail = Application.COUNTA(Range("Hol_Name"))

The evaluate works because it is converting an Excel name, so it is the
same
as if you were doing it in Excel. VBA doesn't, you have to tell VBA
what
objects you are using. In actuality, in your formula you could do what
you
showed, namely using COUNTA(HolAvail).

Regards

Bob

"Andibevan" wrote in message
...
I think I may now have worked out how to do the sumproduct but I am
having
a
problem with a named range:-

If I try:-

HolAvail = CountA(Hol_Name) - It says this is not a defined range
If I use Evaluate("Counta(Hol_Name)")) - it gives me a value?

Any Ideas why? I think once I sort this bit my UDF will work

"Andibevan" wrote in message
...
Bob,

Sorry - I seem to be doing really badly at explaining things.

The reason I want to create a UDF is (1) Stop Users from messing up
the
formulas (2) Make the 1000s of cells that use this formula easier to
develop
and modify for future requirements as it removes the requirement to
copy
and
past the formula (quite often I am asked to modify sheets on the fly
in
meetings and the ability to make changes in 30 seconds rather than 2
minutes
can invaluable)

Hol_End,Hol_Start,Hol_Name and Hol_Type_Code are all named ranges of
the
same size.

They are in a table that has Names, Holiday Start Dates, Holiday
finish
dates and Holiday type (Vacation, public holiday, etc). Each
resource
may
have multiple entries but obviously none will overlap. I think it
may
be
more sensible to merely convert the sumproduct formulae to a udf and
have
2
inputs.

To keep it simple, how do I convert :-
(Hol_Name=A2)*(Hol_Type_Code=1))
to
a
UDF where there is only 1 input (a2)?

Thanks in advance.



"Bob Phillips" wrote in message
...
Andi,

That is a relatively simple SP formula, I wouldn't have thought
necessary
to
put in a UDF.

By declarintg the cell, I2, in relative terme, when you copy to
another
cell, it will update accordingly, which seems to be what you
wanted
to
do.
However, you also seem to test one cell against one cell
(I2=Hol_Start),
and then many cells against one value (Hol_Name="Andi") which is a
no-no
with SP

So I don't get it yet, but to answer your question, here is a
simple
example

Function GetValue(val1, rng1 As Range, val2, rng2 As Range)
Dim sFormula As String
sFormula = "SUMPRODUCT(--("
If TypeName(val1) = "String" Then
sFormula = sFormula & """" & val1 & """"
Else
sFormula = sFormula & val1
End If
sFormula = sFormula & "=" & rng1.Address & "),--("
If TypeName(val2) = "String" Then
sFormula = sFormula & """" & val2
Else
sFormula = sFormula & val2
End If
sFormula = sFormula & "=" & rng2.Address & "))"
GetValue = Evaluate(sFormula)
End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
This may seem to be completely unrelated (probably due to my
poor
explanation) but I am trying to create a UDF for the following
formulae:-






=SUMPRODUCT((I2=Hol_Start)*(I2<=Hol_End)*(Hol_Nam e="Andi")*(Hol_Type_Code))

Hol_Start, Hol_End and Hol_Type_Code are all defined ranges on a
data
table

The reason I was persuing the path before is that cell I2 is
always
in
a
relative position to the cell where the function was.

I am completely stuck on getting Sumproduct into a UDF,
Particularly
when
using named ranges. Thanks for your answer - that knowledge
will
come
in
handy for some other things as well.



"Bob Phillips" wrote in
message
...
I am not absolutely sure what you want Andi, but a UDF would
simply
be

Function myAddress()
myAddress = Application.Caller.Address(False, False)
End Function

but this will only work as a worksheet function, so I can't
quite
see
it's
value.

From within a function, you can get a cell's row and/or column
number
with

rng.Row (.Column)

where rng is any range object, such as Selection, Activecell,
o
r
Range("A1").

Perhaps if you give a bit more detail I can be more helpful.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
Have just had a closer look and I am off the mark for what I
want
to
do.

Is it possible for a UDF to return the address or column of
the
cell
which
it is in. This isn't actually what I need to do but I can
manage
all
the
other parts.

Essentially I have a huge SUMPRODUCT formula which uses
multiple
If
statement and I would find it much easier to put all the
coding
in
a
UDF.
If the UDF new which cell it was running in, I would not
have
any
need
for
any locations for variables to be included.

Thanks

Andi

"Andibevan" wrote in
message
...
Thanks Bob - I was just about to ask exactly that as my
app
needs
non-an
absolute reference

"Bob Phillips" wrote
in
message
...
or MsgBox Activecell.Address(False,False) to get A1
style.

--

HTH

RP
(remove nothere from the email address if mailing
direct)


"Mangesh Yadav" wrote in
message
...
MsgBox ActiveCell.Address

Mangesh



"Andibevan" wrote in
message
...
How do you return the range of the active cell in a
messagebox?
(i.e.
if
A1
is selected it returns A1)

:)

Mangesh


"Bob Phillips" wrote in message
...

"Mangesh Yadav" wrote in message
...
well, it does happen to me too sometimes :)

... sometimes, I wish it were only sometimes :-)