• 414K Members
• 8,741 Online
• 478K Conversations

What to use instead of Activecell.column?

Occasional Contributor

What to use instead of Activecell.column?

I wrote this formula to identify the column of each cell:
Column Function ()
Column = activecell.Column
End of Function
This function works correctly if used, for example, in this way
cells (1,2) = Column
In fact the cells (1,2) display 2, that is the column of the cell.
But if I drag this formula into some cells of line A it happens that all the cells show me the value 2, instead of the value of the column of each cell.
This happens, I believe, because by using drag to copy the formula the starting cell is always active, which is cell A (1,2).
To correct I have to go into each cell and click on the formula.
Is there an automatic way to correct the error?
The solution to the problem would be useful to simplify another program.
Many thanks.

3 Replies
Highlighted

Re: What to use instead of Activecell.column?

Sorry the function is:

Function Colonna()
Colonna = activecell.Column
End Function

Re: What to use instead of Activecell.column?

Hello @SergioSolimena ,

can you describe what you would like to achieve? Excel already has a built-in function to return the number of the column. In English Excel it is COLUMNL(). If you don't provide a parameter it will return the column number of the current column. If you use a cell reference, like =COLUMN(B2) it will return the column number of the referenced cell.

Re: What to use instead of Activecell.column?

@Ingeborg Hawighorst
I give an example to be clearer.
Suppose you have entered values ​​in some cells :, in this way:
A1 = 2;
A2 = 1; A3 = 1; A4 = 0, A5 = -1
B2 = MediaLuminanza (\$ AB \$ 1)
B3 = MediaLuminanza (\$ A \$ 1)
I would like the following results
B2 = (A2 A3) / 2 = 1
B3 = (A3 A4) /2=0.5
So if, later, in A1 I insert 3, I would like to get
B2 = (A2 A3 A4) / 3 = 2/3 = 0.67
B3 = (A3 A4 A5) / 3 = 0/3 = 0
Unfortunately, the MediaLuminanza

@Ingeborg Hawighorst wrote:

Hello @SergioSolimena ,

can you describe what you would like to achieve? Excel already has a built-in function to return the number of the column. In English Excel it is COLUMNL(). If you don't provide a parameter it will return the column number of the current column. If you use a cell reference, like =COLUMN(B2) it will return the column number of the referenced cell.

function does not work automatically.
If instead I click on cell B2 I get the correct result, as well as if I click on B3.
The problem, in my opinion, is that Column and Row pass the correct values ​​only if I click in the cell.
But obviously I would like Column and Row to automatically pass the values ​​of the cells into which the MediaLuminanza function has been inserted, namely the values ​​of cells B2 and B3.

In Modulo1 I wrote the following function:
"
‘Function MediaLuminanza (NPunti)
Dim Xmin As Integer, Xmax As Integer, RigaL As Integer, RigaF As Integer, ColF As Integer, Media As Double
'Initialize
RigaL = 2 'Row B
'Program
ColF = activecell.Column
RigaF = activecell.Row
Media = 0
For k = 1 To NPpoints
Media = Cells (RigaF - 1, ColF k - 1) + Media
Next k
MediaLuminanza = Media / NPunti
‘End Function

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies