How to get "This" cell -- Excel 1

[Wolfie7873]

I have a macro function that returns a letter based on the row of the cell. This function exists in several cells. Currently, I am retrieving the row by

row = ActiveCell.row

The problem with that is that when I recalculate the sheet, all the letters will depend on the active cell, not the individual cells where the calculations take place. How do I retrieve not the active cell, but the cell being calculated?

 

[SkipVought]

Please post ALL your code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Wolfie7873]

Function chi2_cat(p As Double)
 
    row = ActiveCell.row
    
    If (p <= 0.05) Then chi2_cat = ColumnLetter(row - 1) Else chi2_cat = p
            

End Function

Function ColumnLetter(ByVal colNum As Long) As String
    Dim i As Long, x As Long
    For i = Int(Log(CDbl(25 * (CDbl(colNum) + 1))) / Log(26)) - 1 To 0 Step -1
        x = (26 ^ (i + 1) - 1) / 25 - 1
        If colNum > x Then
            ColumnLetter = ColumnLetter & Chr(((colNum - x - 1) \ 26 ^ i) Mod 26 + 65)
        End If
    Next i
End Function

So row 1 is A, row 2 is B, row 3 is C and so forth. But when I recalculate the entire worksheet, everything that qualifies as a letter inherits the same letter as determined by the active cell.

 

[SkipVought]

Function chi2_cat(p As [b]RANGE[/b])
 
    row = [b]p[/b].row
    
    If (p <= 0.05) Then chi2_cat = ColumnLetter(row - 1) Else chi2_cat = p
            

End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ck1999]

Why not in your calling of the function send the row value as a variable, this way it does not work off of activecell?

=chi2_cat(p,row()) <= place in your cells

ck1999

 

[Wolfie7873]

Thanks, Skip!

 

[DrSimon]

Have you considered using the Target object as in Worksheet_Change? I think that would help here.

Simon Rouse