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 |
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. |
All times are GMT +1. The time now is 02:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com