ExcelBanter - Sending the current row number as a parameter to a function

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Sending the current row number as a parameter to a function (https://www.excelbanter.com/excel-programming/373261-sending-current-row-number-parameter-function.html)

Sending the current row number as a parameter to a function

Hi,
This is my first post.
I have a simple VBA function that counts the number of colored cells
(of a certain color), and prints in to the cell which activates the
function.
It works fine but i want change the function så that will become more
'general', meaning that i'll be able to use it from meny other cells.
I want to know if there is any way to sent somthing like
functionName(currentCellRowNumber) insted of functionName(36) like i do
now where i have to change the number manually every time.
I'll appreciate any help i can get.

Sending the current row number as a parameter to a function

This is a UDF I setup to count the number of cells with a matching colorindex
value to the second parameter.
syntax
CountColor(Range of Cell,Colorindex value)
ex
CountColor(A1:B32,14)

Public Function CountColor(R As Range, C As Integer)
Dim cell As Range, Count As Integer
Count = 0
For Each cell In R
If cell.Interior.ColorIndex = C Then
Count = Count + 1
End If
Next cell
CountColor = Count
End Function

I also use this one as an easy way to get the current interior colorindex
value.
Public Function GetColor(R As Range)
GetColor = R.Interior.ColorIndex
End Function

HTH
Cal

" wrote:

Hi,
This is my first post.
I have a simple VBA function that counts the number of colored cells
(of a certain color), and prints in to the cell which activates the
function.
It works fine but i want change the function sÃ¥ that will become more
'general', meaning that i'll be able to use it from meny other cells.
I want to know if there is any way to sent somthing like
functionName(currentCellRowNumber) insted of functionName(36) like i do
now where i have to change the number manually every time.
I'll appreciate any help i can get.

Sending the current row number as a parameter to a function

Thank to everyone.
I finished the function, and now it works fine from every cell by
calling =COUNTBYCOLOR(ROW()). As you can see, i only need to send the
row number because my Range of cells is fixed. Also i need the result
in halv and that is done at the end.
I also have a posibility to count the cells of a specific font color if
OfText is sent with TRUE.
Here it is the finished function:

---------------------------------------------
Function CountByColor(dRow As String, Optional OfText As Boolean =
False) As Double

Dim rRange As Range
Dim sColor As String
Dim sRange As String
Application.Volatile True

sColor = "A" + dRow
sRange = "A" + dRow + ":J" + dRow

WhatColorIndex = Range(sColor).Interior.ColorIndex

For Each rRange In Range(sRange).Cells
If OfText = True Then
CountByColor = CountByColor - (rRange.Font.ColorIndex =
WhatColorIndex)
Else
CountByColor = CountByColor - (rRange.Interior.ColorIndex =
WhatColorIndex)
End If
Next rRange

CountByColor = CountByColor / 2

End Function
---------------------------------------------

Thanks
Amit

Dana DeLouis wrote:
Don't know if this could be useful.
Without sending the Row, it returns the Row number squared.
This has no error checking though.

=SqrRowNumber()

Function SqrRowNumber()
Dim R As Long
R = Application.Caller.Row
SqrRowNumber = R ^ 2
End Function

--
HTH :)
Dana DeLouis
Windows XP & Office 2003

wrote in message
ups.com...
Yes, I just also found out.
Thank all
Amit

wrote:
Yes

=samplefunction(row())

wrote:
Thanks DAD,
but can i send the result of =ROW() as a parameter in my own function?

Die_Another_Day wrote:
Try the Row() function. To test, in a cell type =Row() and press enter

Charles

wrote:
Hi,
This is my first post.
I have a simple VBA function that counts the number of colored cells
(of a certain color), and prints in to the cell which activates the
function.
It works fine but i want change the function så that will become
more
'general', meaning that i'll be able to use it from meny other
cells.
I want to know if there is any way to sent somthing like
functionName(currentCellRowNumber) insted of functionName(36) like i
do
now where i have to change the number manually every time.
I'll appreciate any help i can get.

Sending the current row number as a parameter to a function

Glad it works! If you wish, here's just 2 cents for ideas...

Function CountByColor(dRow As Double, ...etc

WhatColorIndex = Cells(dRow, 1).Interior.ColorIndex
For Each rRange In Cells(dRow, 1).Resize(1, 10).Cells 'To Column J
If OfText Then...

Again, just ideas.
--
HTH :)
Dana DeLouis
Windows XP & Office 2003

wrote in message
oups.com...
Thank to everyone.
I finished the function, and now it works fine from every cell by
calling =COUNTBYCOLOR(ROW()). As you can see, i only need to send the
row number because my Range of cells is fixed. Also i need the result
in halv and that is done at the end.
I also have a posibility to count the cells of a specific font color if
OfText is sent with TRUE.
Here it is the finished function:

---------------------------------------------
Function CountByColor(dRow As String, Optional OfText As Boolean =
False) As Double

Dim rRange As Range
Dim sColor As String
Dim sRange As String
Application.Volatile True

sColor = "A" + dRow
sRange = "A" + dRow + ":J" + dRow

WhatColorIndex = Range(sColor).Interior.ColorIndex

For Each rRange In Range(sRange).Cells
If OfText = True Then

<snip