Excel function does not work when dragging 1

[KellyK]

I created a function within Excel VBA called FindNext(). When I enter "=FindNext()" into each individual cell, I get the desired results. However, if I enter "=FindNext()" into one cell and then drag the formula down a column, the column is populated with the first correct result, all the way down. By that I mean, if FindNext() returns a "7" in row 1, I will get a column full of "7s". However, if I manually paste the formula into each row, I'll get "7", "25", "32", whatever. I put a "DoEvents" in my code thinking that it just needs time to refresh but this did not fix the problem. Does anybody know what could be the issue? My code is below:

Function FindNext() As String

If Cells(ActiveCell.Row, "N") <> Empty Then
FindNext = Cells(ActiveCell.Row, "N")
Exit Function
End If

r = ActiveCell.Row + 1

Do While Cells(r, "N").Value = Empty
r = r + 1
Loop

FindNext = Cells(r, "N").Value

DoEvents

End Function

Kelly

 

[KellyK]

I should also mention that I have Automatic Calculation selected in my formula options and I tried pressing F9 after dragging the formula and it STILL does not put in the correct values. The correct values are there only when I enter the formula into each individual cell.

Kelly

 

[PHV]

Your function should have the cell address as parameter instead of using ActiveCell.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

If you need to refer in UDF to the cell where you call it, use Application.ThisCell or Application.Caller (versions older than xp).

combo

 

[SkipVought]

Kelly,

You need to rethink what you're trying to do.

Here are some observations:

Empty

The Empty keyword is used as a Variant subtype. It indicates an uninitialized variable value.

If a cell contains a formula, it is NOT empty, so the first IF statement is unnecessary. In the other case, use the IsEmpty function. Either

Do While IsEmpty(Cells(r, "N"))
...

or

Do While Cells(r, "N").Value <> ""
...

But all this function seem to do, is return the VALUE of the first row in column N containing a VALUE. Is that the purpose?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[KellyK]

Thanks for the help, guys. I'm going to try the suggestions you've made and see where that gets me. Skip, believe it or not, yes, that is all I'm trying to do. It's kind of similar to populating the blank cells of a pivot table with the value in the cell immediately above, but an upside down version. Hard to explain but our I.T. folks were not able (willing?) to produce a report in the exact format our customer needs, so I wrote this function to clean it up.

Kelly

 

[SkipVought]

If you explain the requirement in more detail, there may be a better solution. An specific example would help.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}