Q:

excel custom function for a loop

I'm a newbie to excel VB so I hope some one can help me with this:
I'm trying to calculate the row number of the first cell which doesn't have the value zero in a column(eg:if it's 0 0 0 1 should give me 4) but have a hard time in making it display on the worksheet,
Here is my code- 
Function Module1(y As Integer)
Dim x As Integer
x = 1
Dim a As Integer
a = 0
Do While x < 100 Or a <> 0
    If Cells(x, y).Value = 1 Then
    a = x

x = x + 1
Loop
Cells(y, 101).Value = a
End Function

y is the column number I want to get this function and there are 100 columns.

A:

You declare a function 'Module1'. Not a good name for a function because 'Module1' used to be the name of the code container 'Module1'.
So:
press Alt+F11, right click on your Project, add a module (autonumbering gives your new module the name Module1, Module2 etc), type this code:
Public Function GetNonZeroRank(r As Range)
x = 1
Do While x <= r.Rows.Count And a = 0
    If r.Cells(x, 1).Value <> 0 Then
    a = x
End If
x = x + 1
Loop
GetNonZeroRank = a
End Function

You can use this function from cell A101: type in the formula:
=GetNonZeroRank(A1:A100)

Thats how to use (Public) Functions
Important: you can't change cell values from inside a function called from a sheet:

Cells(y, 101).Value = a

you can do this inside a Sub method

