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