column number in a user defined function 1

[welldefined]

Hi,

In a cell, I will use a function I am going to write. How to get the column number of this cell in the function?

 

[AnotherHiggins]

try
=column()

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[welldefined]

That is also my first try but not correct.

 

[AnotherHiggins]

Oops. I forgot that this is the VBA forum for a second there.

try:

myCol = ActiveCell.Column

But =Column() is a standard excel function.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[welldefined]

haha, that is my second try: that works except when I drag the fomular to other cells the activeCell remain the same!

 

[welldefined]

Let me explain more detail.
I wrote a function:
Function theColumn()
theColumn = ActiveCell.Column()
End Function
in cell(1,1) I put =theColumn(), it will show 1, fine.
I then drag it from cell(1,1) to cell(1,2), it will still show 1 in cell(1,2) but I want it show 2.

 

[PHV]

Function theColumn(R As Range)
theColumn = R.Column
End Function
in cell(1,1) put =theColumn(A1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rmikesmith]

Add

Application.Volatile True

to the beginning of your function.


Regards,
Mike

 

[PHV]

Mike, have you tried your suggestion ?

 

[AnotherHiggins]

wait, what is the point of this? What is the difference between using the UDF theColumn
[tab]Function theColumn()
[tab]theColumn = ActiveCell.Column()
[tab]End Function

and using =column() ?

What are you actually aiming to do?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[PHV]

What is the difference between using the UDF theColumn() and using =column() ?
One works, the other not ;-)

 

[rmikesmith]

PHV,

Yes. My actual function for testing is

Function MyCustom() As Integer
   Application.Volatile True
   MyCustom = ActiveCell.Column
End Function

Regards,
Mike

 

[PHV]

Don't works for me (XL2003)
What happens if you press the F9 key with several cells having this formula ?

 

[welldefined]

In my real case, in the user defiened function I need to do something based on the column in which the function located.

 

[PHV]

Have you tried my suggestion ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[welldefined]

PHV,

I tried you code and that works.
I am thinking why we need to tell the function "R As Range"....why we cannot use =column() in user defined function?

 

[rmikesmith]

PHV,

D'oh. You are right, of course. I got sidetracked with the thought of dragging the formula around, but my "solution" does not work; yours does.

Mike

 

[GlennUK]

Hi PHV,

you say ...

What is the difference between using the UDF theColumn() and using =column() ?
One works, the other not

I use =Column() all the time, and it does work! Are you sure you are using it correctly ( anotherhiggins knows I'm right on this )?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[PHV]

Glenn, I just said that =Column() works and that Mike's =theColumn() doesn't !

 

[GlennUK]

Ah, wrong end of stick!

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.