UDF IF() Function??

[Clarence Crow]

Hello again

I posted this a couple of weeks ago and was directed to:
http://www.mvps.org/dmcritchie/excel/formula.htm#hasformula , but
couldn't see/figure out what to do (not being big on VB, except for
editing Macros)
Can anyone expand on it for me

"I have an estimate workbook with a Management Summary up front of
numerous Tabbed detailed worksheets.

At the top of the Management Summary Sheet I have the Date shown with
=TODAY() and in the cell to the right I have a text entry "Floating"

When the Management have completed any adjustments, I Copy the Date
and Paste Special/Values over itself and then type a text entry
"Fixed" where "Floating" was.

I was wondering if there was an IF() function that would make the text
entry change from "Floating" to "Fixed" and the Syntax for it?

Clarence "

 

[Norman Harker]

Hi Clarence!

I use the UDF:

Function ISFORMULA(MyCell As Range) As Boolean
ISFORMULA = MyCell.HasFormula
End Function

This is put in an ordinary module in the workbook. For general purpose
use on your computer you can put it in an ordinary module in your
personal.xls workbook. You can also put it in an Add-in that can be
loaded by other computers.

Once you have it, then:

=ISFORMULA(A1)
Returns FALSE if the date is hard coded
Returns TRUE if the date is found using =TODAY()

Now! Remember that the requirement of the first argument of an IF
function is that it should be a condition that returns TRUE or FALSE.

So you can use:

=IF(ISFORMULA(A1),"Floating","Fixed")

But, you might hit problems because A1 might contain a formula that
doesn't return a date. To protect against this I might use another
UDF:

Function ISADATE(MyCell As Range) As Boolean
ISADATE = IsDate(MyCell)
End Function

Again this returns TRUE or FALSE depending upon whether the cell
contains a date or not.

That gives me a little more security and flexibility.

=IF(ISADATE(A1),IF(ISFORMULA(A1),"Floating","Fixed"),"Not a date")

If A1 contains a date then the formula returns Floating or Fixed
depending upon whether or not it is the result of a formula. If it
doesn't contain a date you have a message.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Saturday:Algeria (Independence Day); Cape Verde
Islands (Independence Day); Czech Republic (St. Cyril & St. Methodius
Day); Isle of Man (Tynwald Day); Rwanda (Peace & Unity Day); Slovakia
(St. Cyril & St. Methodius Day); Thailand (Asalha Puja Day); Venezuela
(Independence Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Paul B]

Clarence, see if this will do what you want, put the UDF in a module, to
put in a Macro, press alt and F11 to open the VBA editor, in the left hand
window click on your workbook name, go to insert, module, and paste the code
in the window that opens on the right hand side, press Alt and Q to close
this window and go back to your workbook and press alt and F8, this will
bring up a box to pick the Macro from, click on the Macro name to run it.
If you are using excel 2000 or newer you may have to change the macro
security settings to get the macro to run.

Function HasFormula(cell)
HasFormula = cell.HasFormula
End Function

Then if you were checking for a formula in A1 use this
=IF(HasFormula(A1),"Floating","Fixed")
--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[Clarence Crow]

Hi Clarence!

I use the UDF:

Function ISFORMULA(MyCell As Range) As Boolean
ISFORMULA = MyCell.HasFormula
End Function

This is put in an ordinary module in the workbook. For general purpose
use on your computer you can put it in an ordinary module in your
personal.xls workbook. You can also put it in an Add-in that can be
loaded by other computers.

Once you have it, then:

=ISFORMULA(A1)
Returns FALSE if the date is hard coded
Returns TRUE if the date is found using =TODAY()

Now! Remember that the requirement of the first argument of an IF
function is that it should be a condition that returns TRUE or FALSE.

So you can use:

=IF(ISFORMULA(A1),"Floating","Fixed")

But, you might hit problems because A1 might contain a formula that
doesn't return a date. To protect against this I might use another
UDF:

Function ISADATE(MyCell As Range) As Boolean
ISADATE = IsDate(MyCell)
End Function

Again this returns TRUE or FALSE depending upon whether the cell
contains a date or not.

That gives me a little more security and flexibility.

=IF(ISADATE(A1),IF(ISFORMULA(A1),"Floating","Fixed"),"Not a date")

If A1 contains a date then the formula returns Floating or Fixed
depending upon whether or not it is the result of a formula. If it
doesn't contain a date you have a message.

Thanks I'll give it a shot.
I should really do a Macro to set the Date as well.

Clarence

 

[Clarence Crow]

Clarence, see if this will do what you want, put the UDF in a module, to
put in a Macro, press alt and F11 to open the VBA editor, in the left hand
window click on your workbook name, go to insert, module, and paste the code
in the window that opens on the right hand side, press Alt and Q to close
this window and go back to your workbook and press alt and F8, this will
bring up a box to pick the Macro from, click on the Macro name to run it.
If you are using excel 2000 or newer you may have to change the macro
security settings to get the macro to run.

Function HasFormula(cell)
HasFormula = cell.HasFormula
End Function

Then if you were checking for a formula in A1 use this
=IF(HasFormula(A1),"Floating","Fixed")

Thanks I'll give it a shot.
I should really do a Macro to set the Date as well.

Clarence

 

[Norman Harker]

Hi Clarence!

Re:
"I should really do a Macro to set the Date as well"

You already have a cell that has TODAY() in it. What you could do is a
very simple macro:

Sub FixMyDate()
Range("A1").Value = Date
Range("B1").Value = "Fixed"
End Sub

Now assign it to a shortcut key.

Tools > Macro > Macros
Select "FixMyDate"
Options
Type (eg) in the Shortcut Key input box:
f
Type (eg) in the Description input box:
Puts today's date in A1 and "Fixed" in B1
OK
Cancel [Yes! Strange but true!!]


As an alternative you can assign it to a button on the worksheet that
can be pressed when data entry is complete. Obviously that press and
the macro could lead to lots of other things limited only by your
requirements and the considerable automation capabilities that you can
get out of this program.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Saturday:Algeria (Independence Day); Cape Verde
Islands (Independence Day); Czech Republic (St. Cyril & St. Methodius
Day); Isle of Man (Tynwald Day); Rwanda (Peace & Unity Day); Slovakia
(St. Cyril & St. Methodius Day); Thailand (Asalha Puja Day); Venezuela
(Independence Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Clarence Crow]

Hi Clarence!

Re:
"I should really do a Macro to set the Date as well"

You already have a cell that has TODAY() in it. What you could do is a
very simple macro:

Sub FixMyDate()
Range("A1").Value = Date
Range("B1").Value = "Fixed"
End Sub

Now assign it to a shortcut key.

Tools > Macro > Macros
Select "FixMyDate"
Options
Type (eg) in the Shortcut Key input box:
f
Type (eg) in the Description input box:
Puts today's date in A1 and "Fixed" in B1
OK
Cancel [Yes! Strange but true!!]


As an alternative you can assign it to a button on the worksheet that
can be pressed when data entry is complete. Obviously that press and
the macro could lead to lots of other things limited only by your
requirements and the considerable automation capabilities that you can
get out of this program.

There ya go thanks again
Maybe I'll learn some of this stuff after all, if I lift my head out
of the panic dog-work for a few minutes a day

Clarence

 

[Norman Harker]

Hi Clarence!

Re:
"There ya go thanks again
Maybe I'll learn some of this stuff after all, if I lift my head out
of the panic dog-work for a few minutes a day "

Typical vicious circle! So much work to do and not enough time to
learn how you can do it quicker and more efficiently.

But watch it! Learn how to do it quicker and more efficiently and
you'll get laid off because you haven't got enough work to do! <vbg> +



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Monday: Antigua and Barbuda, Bahamas, Barbados,
Belize, Dominica, Grenada, Guyana, Jamaica, Montserrat, St. Kitts and
Nevis, Saint Lucia, St. Vincent and the Grenadines, Trinidad and
Tobago, Turks and Caicos Islands (All Caricom Day); Bangladesh (Bank
Holiday); Cayman Islands (Constitution Day); Solomon Islands
(Independence Day); Spain (Running of the Bulls); Tanzania (Saba
Saba); Zambia (Heroes Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.