Forum Discussion
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
- Sorry the function is:
Function Colonna()
Colonna = activecell.Column
End FunctionHello 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.
IngeborgHawighorst
thank you for your reply which unfortunately does not answer my question.
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
IngeborgHawighorst 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
