Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Function puzzle
The following sub works OK when called from a subroutine as shown. Sub CopyCyclic(num) Range("a1") = "Q1" Range("a2") = "Q2" Range("a3") = "Q3" Range("a4") = "Q4" k = 0 For Each c In Range("a5:a500") If c.Offset(1, 0) = "" Then c.Value = c.Offset(-4, 0).Value c.Offset(0, 1).Value = num k = k + 1 If k Mod 4 = 0 Then num = num + 1 End If Next End Sub Sub test() [a:a].Clear CopyCyclic (1980) However, its function equivalent fails. Function CopyCyclic(num) Range("a1") = "Q1" Range("a2") = "Q2" Range("a3") = "Q3" Range("a4") = "Q4" k = 0 For Each c In Range("a5:a500") If c.Offset(1, 0) = "" Then c.Value = c.Offset(-4, 0).Value c.Offset(0, 1).Value = num k = k + 1 If k Mod 4 = 0 Then num = num + 1 End If Next Next End Function Entering *=CopyCyclic (1970*), for example, returns an error? An error equally results if it is entered as array (Ctr+Shft+Ent) . Just curious for an explanation. -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=387931 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Function puzzle
a function cannot modify the "environment" like a sub can. it simple "goal in life" is to return a (series of) value based on input. thus try like: Sub CopyCyclic() [a:a].Clear [a5].Resize(80, 2) = QtrCyclic(1980, 20) End Sub you can call it from the sheet too.. enter as a 2 column,x row array function. (with ctrl/shift/enter) (if you leave out the years argument the function resizes the result to match the calling array Function QtrCyclic(start&, Optional years&) As Variant Dim i&, y&, q&, vres 'when called as UDF (entered as array function) 'resize result to size of array If years = 0 And TypeOf Application.Caller Is Range Then years = Application.Caller.Rows.Count \ 4 End If ReDim vres(1 To 4 * years, 1 To 2) For y = start To start + years - 1 For q = 1 To 4 i = i + 1 vres(i, 1) = "Q" & q vres(i, 2) = y Next Next QtrCyclic = vres End Function -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam davidm wrote : The following sub works OK when called from a subroutine as shown. Sub CopyCyclic(num) Range("a1") = "Q1" Range("a2") = "Q2" Range("a3") = "Q3" Range("a4") = "Q4" k = 0 For Each c In Range("a5:a500") If c.Offset(1, 0) = "" Then c.Value = c.Offset(-4, 0).Value c.Offset(0, 1).Value = num k = k + 1 If k Mod 4 = 0 Then num = num + 1 End If Next End Sub Sub test() [a:a].Clear CopyCyclic (1980) However, its function equivalent fails. Function CopyCyclic(num) Range("a1") = "Q1" Range("a2") = "Q2" Range("a3") = "Q3" Range("a4") = "Q4" k = 0 For Each c In Range("a5:a500") If c.Offset(1, 0) = "" Then c.Value = c.Offset(-4, 0).Value c.Offset(0, 1).Value = num k = k + 1 If k Mod 4 = 0 Then num = num + 1 End If Next Next End Function Entering *=CopyCyclic (1970*), for example, returns an error? An error equally results if it is entered as array (Ctr+Shft+Ent) . Just curious for an explanation. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Puzzle | Excel Discussion (Misc queries) | |||
vlookup puzzle | Excel Worksheet Functions | |||
Excel Puzzle | Excel Worksheet Functions | |||
Can you help!!!!! New Puzzle | Excel Discussion (Misc queries) | |||
Randbetween puzzle??!? | Excel Programming |