adam_neb
Board Regular
- Joined
- Jun 9, 2002
- Messages
- 101
I have a complex formula (thanks to barry houdini!) that I use often. The problem is, it's complex!
I created a Macro by painstakingly entering the formula, but to use it I have to assign an alt-key or navigate to the macro Tools - Macro - Macros - Name of Macro.
I would rather have a User Defined Function where I could enter
=aown(CellReference) and get my results.
In "VBA and Macros for Microsoft Excel" Chapter 4 User-Defined Functions give examples of UDF's but I'm not sure how to enter my formula or get the formula into a UDF. Anyone point me in the right direction?
Sub aown()
'
' aown Macro
' Macro recorded 9/7/2006 by ao
'
'
ActiveCell.FormulaR1C1 = _
"=YEAR(RC[-1]-WEEKDAY(RC[-1])+4)&TEXT(INT((RC[-1]-WEEKDAY(RC[-1])-DATE(YEAR(RC[-1]-WEEKDAY(RC[-1])+4),1,4))/7)+2,""-00"")"
Range("J11").Select
End Sub
I created a Macro by painstakingly entering the formula, but to use it I have to assign an alt-key or navigate to the macro Tools - Macro - Macros - Name of Macro.
I would rather have a User Defined Function where I could enter
=aown(CellReference) and get my results.
In "VBA and Macros for Microsoft Excel" Chapter 4 User-Defined Functions give examples of UDF's but I'm not sure how to enter my formula or get the formula into a UDF. Anyone point me in the right direction?
Sub aown()
'
' aown Macro
' Macro recorded 9/7/2006 by ao
'
'
ActiveCell.FormulaR1C1 = _
"=YEAR(RC[-1]-WEEKDAY(RC[-1])+4)&TEXT(INT((RC[-1]-WEEKDAY(RC[-1])-DATE(YEAR(RC[-1]-WEEKDAY(RC[-1])+4),1,4))/7)+2,""-00"")"
Range("J11").Select
End Sub