Hi Claus,

I wrote the function. I am very greatful for your help. Thank you very much for giving your time. This was very helpful for me. The function is like below.The function has one requirement: It has to be initially stated in the first row.

Function myfunc(x As Range, Optional rowno As Range)
If rowno Is Nothing Then Set rowno = Application.Caller
rx = x.Rows.Count
a1 = (rowno.Row - 1) Mod (rx + 1)
a2 = Int((rowno.Row - 1) / rx) + 1
myfunc = Application.WorksheetFunction.Index(x, a1, a2)
End Function


Again thanks a lot.