Really Easy But having a mental Blank

MsgBox ActiveCell.Address

Mangesh



"Andibevan" <[email protected]> wrote in message
news:[email protected]...
> How do you return the range of the active cell in a messagebox? (i.e. if
A1
> is selected it returns A1)
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> MsgBox ActiveCell.Address
>
> Mangesh
>
>
>
> "Andibevan" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> MsgBox ActiveCell.Address
>
> Mangesh
>
>
>
> "Andibevan" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:uySTK#[email protected]...
> Chears Mangesh - That was proving very difficult to find.
>
> "Mangesh Yadav" <[email protected]> wrote in message
> news:[email protected]...
> > MsgBox ActiveCell.Address
> >
> > Mangesh
> >
> >
> >
> > "Andibevan" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:u%[email protected]...
> or MsgBox Activecell.Address(False,False) to get A1 style.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Mangesh Yadav" <[email protected]> wrote in message
> news:[email protected]...
> > MsgBox ActiveCell.Address
> >
> > Mangesh
> >
> >
> >
> > "Andibevan" <[email protected]> wrote in message
> > news:[email protected]...
> > > How do you return the range of the active cell in a messagebox? (i.e.
if
> > A1
> > > is selected it returns A1)
> > >
> > >
> >
> >
>
>

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_Name="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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:eYUuO%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thanks Bob - I was just about to ask exactly that as my app needs
non-an
> > > absolute reference
> > >
> > > "Bob Phillips" <[email protected]> wrote in message
> > > news:u%[email protected]...
> > > > or MsgBox Activecell.Address(False,False) to get A1 style.
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Mangesh Yadav" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > MsgBox ActiveCell.Address
> > > > >
> > > > > Mangesh
> > > > >
> > > > >
> > > > >
> > > > > "Andibevan" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > How do you return the range of the active cell in a messagebox?
> > (i.e.
> > > if
> > > > > A1
> > > > > > is selected it returns A1)
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

"Mangesh Yadav" <[email protected]> wrote in message
news:[email protected]...
> well, it does happen to me too sometimes

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

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" <[email protected]> wrote in message
news:eYUuO%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Bob - I was just about to ask exactly that as my app needs non-an
> > absolute reference
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:u%[email protected]...
> > > or MsgBox Activecell.Address(False,False) to get A1 style.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Mangesh Yadav" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > MsgBox ActiveCell.Address
> > > >
> > > > Mangesh
> > > >
> > > >
> > > >
> > > > "Andibevan" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bob - I was just about to ask exactly that as my app needs non-an
> absolute reference
>
> "Bob Phillips" <[email protected]> wrote in message
> news:u%[email protected]...
> > or MsgBox Activecell.Address(False,False) to get A1 style.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Mangesh Yadav" <[email protected]> wrote in message
> > news:[email protected]...
> > > MsgBox ActiveCell.Address
> > >
> > > Mangesh
> > >
> > >
> > >
> > > "Andibevan" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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_Name="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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:eYUuO%[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Thanks Bob - I was just about to ask exactly that as my app needs
> non-an
> > > > absolute reference
> > > >
> > > > "Bob Phillips" <[email protected]> wrote in message
> > > > news:u%[email protected]...
> > > > > or MsgBox Activecell.Address(False,False) to get A1 style.
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "Mangesh Yadav" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > MsgBox ActiveCell.Address
> > > > > >
> > > > > > Mangesh
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Andibevan" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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_Name="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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:eYUuO%[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Thanks Bob - I was just about to ask exactly that as my app needs
> > non-an
> > > > > absolute reference
> > > > >
> > > > > "Bob Phillips" <[email protected]> wrote in
message
> > > > > news:u%[email protected]...
> > > > > > or MsgBox Activecell.Address(False,False) to get A1 style.
> > > > > >
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > RP
> > > > > > (remove nothere from the email address if mailing direct)
> > > > > >
> > > > > >
> > > > > > "Mangesh Yadav" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > MsgBox ActiveCell.Address
> > > > > > >
> > > > > > > Mangesh
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Andibevan" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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_Name="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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:eYUuO%[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Thanks Bob - I was just about to ask exactly that as my app
needs
> > > non-an
> > > > > > absolute reference
> > > > > >
> > > > > > "Bob Phillips" <[email protected]> wrote in
> message
> > > > > > news:u%[email protected]...
> > > > > > > or MsgBox Activecell.Address(False,False) to get A1 style.
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > HTH
> > > > > > >
> > > > > > > RP
> > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > >
> > > > > > >
> > > > > > > "Mangesh Yadav" <[email protected]> wrote in
message
> > > > > > > news:[email protected]...
> > > > > > > > MsgBox ActiveCell.Address
> > > > > > > >
> > > > > > > > Mangesh
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > "Andibevan" <[email protected]> wrote in
message
> > > > > > > > news:[email protected]...
> > > > > > > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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_Name="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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > news:eYUuO%[email protected]...
> > > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Thanks Bob - I was just about to ask exactly that as my app
> needs
> > > > non-an
> > > > > > > absolute reference
> > > > > > >
> > > > > > > "Bob Phillips" <[email protected]> wrote in
> > message
> > > > > > > news:u%[email protected]...
> > > > > > > > or MsgBox Activecell.Address(False,False) to get A1 style.
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > RP
> > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > >
> > > > > > > >
> > > > > > > > "Mangesh Yadav" <[email protected]> wrote in
> message
> > > > > > > > news:[email protected]...
> > > > > > > > > MsgBox ActiveCell.Address
> > > > > > > > >
> > > > > > > > > Mangesh
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Andibevan" <[email protected]> wrote in
> message
> > > > > > > > > news:[email protected]...
> > > > > > > > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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_Name="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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > news:eYUuO%[email protected]...
> > > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Thanks Bob - I was just about to ask exactly that as my app
> needs
> > > > non-an
> > > > > > > absolute reference
> > > > > > >
> > > > > > > "Bob Phillips" <[email protected]> wrote in
> > message
> > > > > > > news:u%[email protected]...
> > > > > > > > or MsgBox Activecell.Address(False,False) to get A1 style.
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > RP
> > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > >
> > > > > > > >
> > > > > > > > "Mangesh Yadav" <[email protected]> wrote in
> message
> > > > > > > > news:[email protected]...
> > > > > > > > > MsgBox ActiveCell.Address
> > > > > > > > >
> > > > > > > > > Mangesh
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Andibevan" <[email protected]> wrote in
> message
> > > > > > > > > news:[email protected]...
> > > > > > > > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > 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" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > > > 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_Name="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" <[email protected]> wrote in message
>> > > > news:[email protected]...
>> > > > > 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" <[email protected]> wrote in message
>> > > > > news:eYUuO%[email protected]...
>> > > > > > 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" <[email protected]> wrote in message
>> > > > > > news:[email protected]...
>> > > > > > > Thanks Bob - I was just about to ask exactly that as my app
>> needs
>> > > > non-an
>> > > > > > > absolute reference
>> > > > > > >
>> > > > > > > "Bob Phillips" <[email protected]> wrote in
>> > message
>> > > > > > > news:u%[email protected]...
>> > > > > > > > or MsgBox Activecell.Address(False,False) to get A1 style.
>> > > > > > > >
>> > > > > > > > --
>> > > > > > > >
>> > > > > > > > HTH
>> > > > > > > >
>> > > > > > > > RP
>> > > > > > > > (remove nothere from the email address if mailing direct)
>> > > > > > > >
>> > > > > > > >
>> > > > > > > > "Mangesh Yadav" <[email protected]> wrote in
>> message
>> > > > > > > > news:[email protected]...
>> > > > > > > > > MsgBox ActiveCell.Address
>> > > > > > > > >
>> > > > > > > > > Mangesh
>> > > > > > > > >
>> > > > > > > > >
>> > > > > > > > >
>> > > > > > > > > "Andibevan" <[email protected]> wrote in
>> message
>> > > > > > > > > news:[email protected]...
>> > > > > > > > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > > 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" <[email protected]> wrote in message
> >> > > news:[email protected]...
> >> > > > 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_Name="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" <[email protected]> wrote in
message
> >> > > > news:[email protected]...
> >> > > > > 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" <[email protected]> wrote in message
> >> > > > > news:eYUuO%[email protected]...
> >> > > > > > 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" <[email protected]> wrote in message
> >> > > > > > news:[email protected]...
> >> > > > > > > Thanks Bob - I was just about to ask exactly that as my app
> >> needs
> >> > > > non-an
> >> > > > > > > absolute reference
> >> > > > > > >
> >> > > > > > > "Bob Phillips" <[email protected]> wrote in
> >> > message
> >> > > > > > > news:u%[email protected]...
> >> > > > > > > > or MsgBox Activecell.Address(False,False) to get A1
style.
> >> > > > > > > >
> >> > > > > > > > --
> >> > > > > > > >
> >> > > > > > > > HTH
> >> > > > > > > >
> >> > > > > > > > RP
> >> > > > > > > > (remove nothere from the email address if mailing direct)
> >> > > > > > > >
> >> > > > > > > >
> >> > > > > > > > "Mangesh Yadav" <[email protected]> wrote in
> >> message
> >> > > > > > > > news:[email protected]...
> >> > > > > > > > > MsgBox ActiveCell.Address
> >> > > > > > > > >
> >> > > > > > > > > Mangesh
> >> > > > > > > > >
> >> > > > > > > > >
> >> > > > > > > > >
> >> > > > > > > > > "Andibevan" <[email protected]> wrote in
> >> message
> >> > > > > > > > > news:[email protected]...
> >> > > > > > > > > > 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" <[email protected]> wrote in message
>> >> news:%[email protected]...
>> >> > 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" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> > > 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" <[email protected]> wrote in message
>> >> > > news:[email protected]...
>> >> > > > 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_Name="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" <[email protected]> wrote in
> message
>> >> > > > news:[email protected]...
>> >> > > > > 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" <[email protected]> wrote in message
>> >> > > > > news:eYUuO%[email protected]...
>> >> > > > > > 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" <[email protected]> wrote in
>> >> > > > > > message
>> >> > > > > > news:[email protected]...
>> >> > > > > > > Thanks Bob - I was just about to ask exactly that as my
>> >> > > > > > > app
>> >> needs
>> >> > > > non-an
>> >> > > > > > > absolute reference
>> >> > > > > > >
>> >> > > > > > > "Bob Phillips" <[email protected]> wrote
>> >> > > > > > > in
>> >> > message
>> >> > > > > > > news:u%[email protected]...
>> >> > > > > > > > or MsgBox Activecell.Address(False,False) to get A1
> style.
>> >> > > > > > > >
>> >> > > > > > > > --
>> >> > > > > > > >
>> >> > > > > > > > HTH
>> >> > > > > > > >
>> >> > > > > > > > RP
>> >> > > > > > > > (remove nothere from the email address if mailing
>> >> > > > > > > > direct)
>> >> > > > > > > >
>> >> > > > > > > >
>> >> > > > > > > > "Mangesh Yadav" <[email protected]> wrote in
>> >> message
>> >> > > > > > > > news:[email protected]...
>> >> > > > > > > > > MsgBox ActiveCell.Address
>> >> > > > > > > > >
>> >> > > > > > > > > Mangesh
>> >> > > > > > > > >
>> >> > > > > > > > >
>> >> > > > > > > > >
>> >> > > > > > > > > "Andibevan" <[email protected]> wrote in
>> >> message
>> >> > > > > > > > > news:[email protected]...
>> >> > > > > > > > > > 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" <[email protected]> wrote in message
news:[email protected]...
>
> "Mangesh Yadav" <[email protected]> wrote in message
> news:[email protected]...
> > well, it does happen to me too sometimes
>
> ... sometimes, I wish it were only sometimes :-)
>
>