user defined function which increase consecutively while passingbelow cells

[oercim]

I need help about a subject. I won't state my real problem here since it will take long time to you. But if I understand the below problem's solution,I guess I can solve my real problem.


I want to define my own function in excel. Let name of that function be "myfunc". Now, let use this function in a cell in a sheet. I want it return "1" in the initial cell. When I pass this function to below cell, then I wantit return "2". As I pass it below I want return value increase 1 by 1. Howcan be defined such function in vba.


I will be very glad if you help me. Thanks a lot.

 

[oercim]

I wrote such a function:

Function myfunc()
a=ActiveCell.Row
myfunc=a
End Function


However, that does not work as I want. In active cell it works fine, but if I fetch the cell from it corner to below cell, it does not work. How can I do that? Thanks alot. That is important to me.

 

[Claus Busch]

hi Oercim,

Am Tue, 5 Mar 2013 03:54:27 -0800 (PST) schrieb oercim:

However, that does not work as I want. In active cell it works fine, but if I fetch the cell from it corner to below cell, it does not work. How can I do that? Thanks alot. That is important to me.

look that there is a empty cell above and try the macro:

Function myfunc(Optional rngC As Range) As Long
If rngC Is Nothing Then Set rngC = Application.Caller
myfunc = rngC.Offset(-1, 0) + 1
End Function

Better way:
Write 1 in the selected cell and drag it down with pressed CTRL-Button


Regards
Claus Busch

 

[oercim]

Hello Claus, that worked very well. I am very glad. Thanks for the help. I just want to learn ne last thing. I searched in google, but I couldn't understand "Caller" at your code. What is its function? What does it do?

 

[Claus Busch]

Hi Oercim,

Am Tue, 5 Mar 2013 04:55:29 -0800 (PST) schrieb oercim:

Hello Claus, that worked very well. I am very glad. Thanks for the help. I just want to learn ne last thing. I searched in google, but I couldn't understand "Caller" at your code. What is its function? What does it do?

the Application.Caller is the cell from which you call your function.

Regards
Claus Busch

 

[oercim]

Thanks a lot Claus. That was very useful for me. Best wishes.

 

[oercim]

5 Mart 2013 Salı 15:11:36 UTC+2 tarihinde oercim yazdı:

Thanks a lot Claus. That was very useful for me. Best wishes.

Hello Claus. I have one more problem with this function. I want initial value to be "1" whatever the above of the initial value when the optional is nothing. Thanks a lot again.

 

[Claus Busch]

Hi Oercim,

Am Wed, 6 Mar 2013 23:01:37 -0800 (PST) schrieb oercim:

Hello Claus. I have one more problem with this function. I want initial value to be "1" whatever the above of the initial value when the optional is nothing. Thanks a lot again.

try:

Function myfunc(Optional rngC As Range) As Long
If rngC Is Nothing Then Set rngC = Application.Caller

myfunc = IIf(rngC.Offset(-1, 0).Formula Like "=myf*", _
rngC.Offset(-1, 0) + 1, 1)

End Function


Regards
Claus Busch

 

[oercim]

Hi, Claus,

I am very greatful for your help. I learned a lot of things from this forum.. Thank you very much. I know, I said many times "this is last". But I haveone more problem.

I want to modify this function a little bit. Let my function returns always"1" as I drag the function down. But while doing this, I want to store above's function's values as variable; let say this variable's name is "x". I mean, the function will return always 1 as I drag the function down, but x will increase consecutively (like 1,2,3,...). But x will not be dispayed inthe sheet. It will just stored, and assigned to draged cell. This is realythe last one.

I will be very glad. Thanks a lot again and again.

 

[Claus Busch]

Hi Oercim,

Am Thu, 7 Mar 2013 05:02:05 -0800 (PST) schrieb oercim:

I want to modify this function a little bit. Let my function returns always "1" as I drag the function down. But while doing this, I want to store above's function's values as variable; let say this variable's name is "x". I mean, the function will return always 1 as I drag the function down, but x will increase consecutively (like 1,2,3,...). But x will not be dispayed in the sheet. It will just stored, and assigned to draged cell. This is realy the last one.

why do you need this function? I am asking that because I am searching a
new and better way to do it.


Regards
Claus Busch

 

[oercim]

Hi Claus,
My real problem is that, I want to move multiple columns into one column. In google I found such a trick:

=INDEX($A$2:$D$25,MOD(ROWS(F$2:F2)-1,ROWS($A$2:$D$25))+1,INT((ROWS(F$2:F2)-1)/ROWS($A$2:$D$25))+1)

which makes columns of table -data inserted in A225- moves to one column which starts from F2.
I want to make a user defined function which makes the same job.

I thought that if I find a solution to the initial problem, I would solve this problem. But I couldn't solve. However, your solutions to the initial problem are very useful for me.
I am very greautful for the help. Thanks a lot.

 

[Claus Busch]

Hi Oercim,

Am Fri, 8 Mar 2013 02:10:35 -0800 (PST) schrieb oercim:

which makes columns of table -data inserted in A225- moves to one column which starts from F2.
I want to make a user defined function which makes the same job.

in F2 try:
=INDEX(A,(ROW()-2)/4+2,MOD(ROW()-2,4)+1)
and copy down


Regards
Claus Busch

 

[oercim]

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.