Home

What to use instead of Activecell.column?

%3CLINGO-SUB%20id%3D%22lingo-sub-410200%22%20slang%3D%22en-US%22%3EWhat%20to%20use%20instead%20of%20Activecell.column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-410200%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3EI%20wrote%20this%20formula%20to%20identify%20the%20column%20of%20each%20cell%3A%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EColumn%20Function%20()%3C%2FSPAN%3E%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3CSPAN%3EColumn%20%3D%20activecell.Column%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EEnd%20of%20Function%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EThis%20function%20works%20correctly%20if%20used%2C%20for%20example%2C%20in%20this%20way%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3Ecells%20(1%2C2)%20%3D%20Column%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EIn%20fact%20the%20cells%20(1%2C2)%20display%202%2C%20that%20is%20the%20column%20of%20the%20cell.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EBut%20if%20I%20drag%20this%20formula%20into%20some%20cells%20of%20line%20A%20it%20happens%20that%20all%20the%20cells%20show%20me%20the%20value%202%2C%20instead%20of%20the%20value%20of%20the%20column%20of%20each%20cell.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EThis%20happens%2C%20I%20believe%2C%20because%20by%20using%20drag%20to%20copy%20the%20formula%20the%20starting%20cell%20is%20always%20active%2C%20which%20is%20cell%20A%20(1%2C2).%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ETo%20correct%20I%20have%20to%20go%20into%20each%20cell%20and%20click%20on%20the%20formula.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EIs%20there%20an%20automatic%20way%20to%20correct%20the%20error%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EThe%20solution%20to%20the%20problem%20would%20be%20useful%20to%20simplify%20another%20program.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EMany%20thanks.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-410200%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-410670%22%20slang%3D%22en-US%22%3ERe%3A%20What%20to%20use%20instead%20of%20Activecell.column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-410670%22%20slang%3D%22en-US%22%3ESorry%20the%20function%20is%3A%3CBR%20%2F%3E%3CBR%20%2F%3EFunction%20Colonna()%3CBR%20%2F%3EColonna%20%3D%20activecell.Column%3CBR%20%2F%3EEnd%20Function%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-411794%22%20slang%3D%22en-US%22%3ERe%3A%20What%20to%20use%20instead%20of%20Activecell.column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-411794%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F314091%22%20target%3D%22_blank%22%3E%40SergioSolimena%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ecan%20you%20describe%20what%20you%20would%20like%20to%20achieve%3F%20Excel%20already%20has%20a%20built-in%20function%20to%20return%20the%20number%20of%20the%20column.%20In%20English%20Excel%20it%20is%20COLUMNL().%20If%20you%20don't%20provide%20a%20parameter%20it%20will%20return%20the%20column%20number%20of%20the%20current%20column.%20If%20you%20use%20a%20cell%20reference%2C%20like%20%3DCOLUMN(B2)%20it%20will%20return%20the%20column%20number%20of%20the%20referenced%20cell.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-419030%22%20slang%3D%22en-US%22%3ERe%3A%20What%20to%20use%20instead%20of%20Activecell.column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-419030%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3Ethank%20you%20for%20your%20reply%20which%20unfortunately%20does%20not%20answer%20my%20question.%3CBR%20%2F%3EI%20give%20an%20example%20to%20be%20clearer.%3CBR%20%2F%3ESuppose%20you%20have%20entered%20values%20in%20some%20cells%20%3A%2C%20in%20this%20way%3A%3CBR%20%2F%3EA1%20%3D%202%3B%3CBR%20%2F%3EA2%20%3D%201%3B%20A3%20%3D%201%3B%20A4%20%3D%200%2C%20A5%20%3D%20-1%3CBR%20%2F%3EB2%20%3D%20MediaLuminanza%20(%24%20AB%20%24%201)%3CBR%20%2F%3EB3%20%3D%20MediaLuminanza%20(%24%20A%20%24%201)%3CBR%20%2F%3EI%20would%20like%20the%20following%20results%3CBR%20%2F%3EB2%20%3D%20(A2%20A3)%20%2F%202%20%3D%201%3CBR%20%2F%3EB3%20%3D%20(A3%20A4)%20%2F2%3D0.5%3CBR%20%2F%3ESo%20if%2C%20later%2C%20in%20A1%20I%20insert%203%2C%20I%20would%20like%20to%20get%3CBR%20%2F%3EB2%20%3D%20(A2%20A3%20A4)%20%2F%203%20%3D%202%2F3%20%3D%200.67%3CBR%20%2F%3EB3%20%3D%20(A3%20A4%20A5)%20%2F%203%20%3D%200%2F3%20%3D%200%3CBR%20%2F%3EUnfortunately%2C%20the%20MediaLuminanza%3C%2FSPAN%3E%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F314091%22%20target%3D%22_blank%22%3E%40SergioSolimena%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20you%20describe%20what%20you%20would%20like%20to%20achieve%3F%20Excel%20already%20has%20a%20built-in%20function%20to%20return%20the%20number%20of%20the%20column.%20In%20English%20Excel%20it%20is%20COLUMNL().%20If%20you%20don't%20provide%20a%20parameter%20it%20will%20return%20the%20column%20number%20of%20the%20current%20column.%20If%20you%20use%20a%20cell%20reference%2C%20like%20%3DCOLUMN(B2)%20it%20will%20return%20the%20column%20number%20of%20the%20referenced%20cell.%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CBR%20%2F%3Efunction%20does%20not%20work%20automatically.%3CBR%20%2F%3EIf%20instead%20I%20click%20on%20cell%20B2%20I%20get%20the%20correct%20result%2C%20as%20well%20as%20if%20I%20click%20on%20B3.%3CBR%20%2F%3EThe%20problem%2C%20in%20my%20opinion%2C%20is%20that%20Column%20and%20Row%20pass%20the%20correct%20values%20only%20if%20I%20click%20in%20the%20cell.%3CBR%20%2F%3EBut%20obviously%20I%20would%20like%20Column%20and%20Row%20to%20automatically%20pass%20the%20values%20of%20the%20cells%20into%20which%20the%20MediaLuminanza%20function%20has%20been%20inserted%2C%20namely%20the%20values%20of%20cells%20B2%20and%20B3.%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20Modulo1%20I%20wrote%20the%20following%20function%3A%3CBR%20%2F%3E%22%3CBR%20%2F%3E%E2%80%98Function%20MediaLuminanza%20(NPunti)%3CBR%20%2F%3EDim%20Xmin%20As%20Integer%2C%20Xmax%20As%20Integer%2C%20RigaL%20As%20Integer%2C%20RigaF%20As%20Integer%2C%20ColF%20As%20Integer%2C%20Media%20As%20Double%3CBR%20%2F%3E'Initialize%3CBR%20%2F%3E%26nbsp%3BRigaL%20%3D%202%20'Row%20B%3CBR%20%2F%3E'Program%3CBR%20%2F%3E%26nbsp%3BColF%20%3D%20activecell.Column%3CBR%20%2F%3E%26nbsp%3BRigaF%20%3D%20activecell.Row%3CBR%20%2F%3E%26nbsp%3BMedia%20%3D%200%3CBR%20%2F%3E%26nbsp%3BFor%20k%20%3D%201%20To%20NPpoints%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3BMedia%20%3D%20Cells%20(RigaF%20-%201%2C%20ColF%20k%20-%201)%20%2B%20Media%3CBR%20%2F%3E%26nbsp%3BNext%20k%3CBR%20%2F%3E%26nbsp%3BMediaLuminanza%20%3D%20Media%20%2F%20NPunti%3CBR%20%2F%3E%E2%80%98End%20Function%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-418449%22%20slang%3D%22en-US%22%3ERe%3A%20What%20to%20use%20instead%20of%20Activecell.column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-418449%22%20slang%3D%22en-US%22%3E%3CBR%20%2F%3EHello%20Ingeborg%20Hawighorst%3CBR%20%2F%3Ethank%20you%20for%20your%20answer%20which%20in%20any%20case%20doesn't%20answer%20my%20question.%3CBR%20%2F%3EI%20give%20an%20example%20to%20be%20clearer.%3CBR%20%2F%3ESuppose%20that%20you%20have%20entered%20values%20in%20some%20cells%20%3A%2C%20in%20this%20way%3A%3CBR%20%2F%3EA1%20%3D%202%3B%3CBR%20%2F%3EA2%20%3D%201%3B%20A3%20%3D%201%3B%20A4%20%3D%200%2C%20A5%20%3D%20-1%3CBR%20%2F%3EB2%20%3D%20MediaLuminanza%20(%24%20A%24%201)%3CBR%20%2F%3EB3%20%3D%20MediaLuminanza%20(%24%20A%20%24%201)%3CBR%20%2F%3EI%20would%20like%20the%20following%20results%3CBR%20%2F%3EB2%20%3D%20(A2%2B%20A3)%20%2F%202%20%3D%201%3CBR%20%2F%3EB3%20%3D%20(A3%20%2B%20A4)%20%2F2%3D0.5%3CBR%20%2F%3ESo%20if%2C%20later%2C%20in%20A1%20I%20insert%203%2C%20I%20would%20like%20to%20get%3CBR%20%2F%3EB2%20%3D%20(A2%20%2B%20A3%20%2B%20A4)%20%2F%203%20%3D%202%2F3%20%3D%200.67%3CBR%20%2F%3EB3%20%3D%20(A3%20%2B%20A4%20%2B%20A5)%20%2F%203%20%3D%200%2F3%20%3D%200%3CBR%20%2F%3EUnfortunately%2C%20the%20MediaLuminanza%20function%20does%20not%20work%20automatically.%3CBR%20%2F%3EIf%20instead%20I%20click%20on%20cell%20B2%20I%20get%20the%20correct%20result%2C%20as%20well%20as%20if%20I%20click%20on%20B3.%3CBR%20%2F%3EThe%20problem%2C%20in%20my%20opinion%2C%20is%20that%20Column%20and%20Row%20pass%20the%20correct%20values%20only%20if%20I%20click%20in%20the%20cell.%3CBR%20%2F%3EBut%20obviously%20I%20would%20like%20Column%20and%20Row%20to%20automatically%20pass%20the%20values%20of%20the%20cells%20into%20which%20the%20MediaLuminance%20function%20has%20been%20inserted%2C%20namely%20the%20values%20of%20cells%20B2%20and%20B3%3CBR%20%2F%3EThanks%20for%20your%20suggestion.%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20Modulo1%20I%20wrote%20the%20following%20function%3A%3CBR%20%2F%3E%22%3CBR%20%2F%3EFunction%20MediaLuminance%20(NPpoints)%3CBR%20%2F%3EDim%20Xmin%20As%20Integer%2C%20Xmax%20As%20Integer%2C%20RigaL%20As%20Integer%2C%20RigaF%20As%20Integer%2C%20ColF%20As%20Integer%2C%20Media%20As%20Double%3CBR%20%2F%3E'Initialize%3CBR%20%2F%3ERigaL%20%3D%202%20'Row%20B%3CBR%20%2F%3E'Program%3CBR%20%2F%3EColF%20%3D%20activecell.Column%3CBR%20%2F%3ERigaF%20%3D%20activecell.Row%3CBR%20%2F%3EAverage%20%3D%200%3CBR%20%2F%3EFor%20k%20%3D%201%20To%20NPpoints%3CBR%20%2F%3EMedia%20%3D%20Cells%20(RigaF%20-%201%2C%20ColF%20k%20-%201)%20Average%3CBR%20%2F%3ENext%20k%3CBR%20%2F%3EMediaLuminance%20%3D%20Average%20%2F%20NPoints%3CBR%20%2F%3EEnd%20Function%3C%2FLINGO-BODY%3E
Highlighted
SergioSolimena
Occasional Contributor

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 Function

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.

@Ingeborg Hawighorst 
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


@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
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies