user defined function which increase consecutively while passingbelow cells

  • Thread starter Thread starter oercim
  • Start date Start date
O

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.
 
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.
 
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.
Click to expand...

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
 
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?
 
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?
Click to expand...

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

Regards
Claus Busch
 
5 Mart 2013 Salı 15:11:36 UTC+2 tarihinde oercim yazdı:
Thanks a lot Claus. That was very useful for me. Best wishes.
Click to expand...

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.
 
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.
Click to expand...

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
 
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.
 
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.
Click to expand...

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
 
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 A2:D25- 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.
 
Hi Oercim,

Am Fri, 8 Mar 2013 02:10:35 -0800 (PST) schrieb oercim:
which makes columns of table -data inserted in A2:D25- moves to one column which starts from F2.
I want to make a user defined function which makes the same job.
Click to expand...

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


Regards
Claus Busch
 
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.
 
Back
Top