excel vba: function return value strange

Question

I have an EXCEL VBA function which should return the address of the first cell where the cell value is greater zero but it is not working. Does anyone has an idea why?

Code:

Function FindNextFilledCell(RowArray() As Integer, ColArray() As Integer)

For i = UBound(ColArray) To 0
    For j = UBound(RowArray) To 0
        CellValue = cells(RowArray(j), ColArray(i)).Value
        If CellValue > 0 Then
            FindNextFilledCell = cells(RowArray(j), ColArray(i)).Address(False, False)
            Exit Function
        End If
    Next j
Next i

End Function

To start with a backwards For loop needs a Step -1 to descend by 1, For i = UBound(ColArray) To 0 Step -1. How are you calling this function? — brettdj
– brettdj, Commented Jan 20, 2012 at 12:29
@brettdj: I have never seen so far a negative UBound... I would say you should post this as an answer :) — JMax
– JMax, Commented Jan 20, 2012 at 12:38
@Jmax Perhaps I should have :). But I didn't as I think the function should be reworked once we understand what it is doing - I doubt the loop is neccesary — brettdj
– brettdj, Commented Jan 20, 2012 at 12:41
@brettdj. Sorry your comment was before my first answer. You get warned if some one has posted an answer while you were typing but not if they post an answer. — Tony Dallimore
– Tony Dallimore, Commented Jan 20, 2012 at 12:45
@TonyDallimore You have nothing to apologise for, it was a lineball call for me to post this as an answer or comment. — brettdj
– brettdj, Commented Jan 20, 2012 at 12:52

Tony Dallimore · Accepted Answer · 2012-01-20 12:42:50Z

2

I am still trying to understand what you are trying to do but I suggest the first error is:

For i = UBound(ColArray) To 0

instead of

For i = UBound(ColArray) To 0 step -1

This assumes you meant to search the cells in reverse order. If you did not

For i = 0 to UBound(ColArray)

might be better.

The next error I have spotted is that you have not defined the type of the value returned by the function. Try:

FindNextFilledCell(RowArray() As Integer, ColArray() As Integer) As String

edited Jan 20, 2012 at 12:42

answered Jan 20, 2012 at 12:34

Tony Dallimore

12.4k7 gold badges35 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

excel vba: function return value strange

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related