CODEWITHSUNDEEP
loopsexcelvba
DUB
DUB

Reputation: 25

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.

Upvotes: 0

Views: 2203

Answers (2)

Ioannis
Ioannis

Reputation: 5388

A few points, in addition to those made by the other responses:

  • By your code it seems that you are looping over 100 rows and the result is displayed in row x (which is the first non zero row of column y) and column 101. Is this what you really want?

  • Why a function and not a subroutine? Unless you want to use it from the spreadsheet (in which case you can change only the content of the cell from which you call it, generally speaking), a function is not useful here. It would be useful if you would assign the return value of a function to another variable.

  • your code will crash if you go more than row 32,767, which is the biggest number an Integer can hold. I can see that you need your code to run until row 100, but I would suggest using Long, which is more robust and actually faster to evaluate.

  • Your code exits the loop under the condition a<>0 when a cell value equals one. In your description you state that you want to get the row number of the first non-zero cell, which, in general, it does not have to be the first non-zero cell that equals to 1. Which of the two do you actually want?

  • Actually, there is a trick that avoids the loop altogether (see below).

     Sub test1()
    test 1
End Sub

' Optional passes a default value to the argument. 
' If no value is given here,  then y = 1
Sub test(Optional y As Long = 1) 
  Dim x As Long
  With Sheet1
    x = .Evaluate("MATCH(1,--(" & .Range(.Cells(1, y), .Cells(100, y)).Address & "<>0),0)")
      .Cells(101, y) = x
  End With
End Sub

The above uses EVALUATE to parse an excel formula.

The idea is that

  • The array part is (example for column A) A1:A100<>0, which is a binary array that looks like {FALSE, FALSE, ..., TRUE, ...}
  • --{FALSE, FALSE, ..., TRUE, ...} Evaluates to {0, 0, ..., 1, ...}
  • MATCH(1,{0,0, ...,1, ...},0) returns the position of the first 1 that is found
  • I would also wrap this expression in an IFERROR statement to control what happens if all values are 0.

We can actually test the excel version of this formula in a cell. It has to be array entered (confirmed with Control + Shift + Enter), which happens automatically when entered from VBA.

I hope this helps!

Upvotes: 0

anefeletos
anefeletos

Reputation: 702

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

Upvotes: 1

Related Questions