How do I get the function to reference the cell it is in?
I am writing a function and I need a function to reference the offset of the cell it is from. I have rows of data with each column containing a specific variable. The function is in the last column and must check if each variable in it matches the variables in the previous row. Each match should take +1 to the final function value. Here is a short version of my function.
Public Function cellscore(testvar1)
totalscore = 0
If testvar1= activecell.Offset(-1, -10) Then
cellscore = totalscore + 1
End If
End Function
What can I replace activecell so that the function works correctly for every row of data I have?
I'm new to VBA, so please bear with me if this is a simple question. Thanks in advance.
source to share
The easiest way is to pass a numeric value to a function that represents the line number. This means your code will become something like:
Public Function cellscore(testvar1,RowNum as Long)
totalscore = 0
If testvar1= Range("SOMECELL").Offset(RowNum-2, -10) Then
cellscore = totalscore + 1
End If
End Function
You will need to change SOMECELL to represent the cell on the first row. For example, if your formula fits in column T , you replace SOMECELL with T1 .
The smart part now has to do with how you call the function inside the worksheet. You do it using the formula:
=cellscore(SOMEVALUE,ROW())
Obviously replace SOMEVALUE with the value you are testing. Now, no matter what line is in this formula, it will refer to the current one when the function is called. Hope this helps!
source to share