.Findnext() not working in userdefined function

[lokiluke]

hi,

i have written a UDF that uses .find to search for a value on another sheet, if it finds it, it will check a certain column to see if it has a value. if it has a value it returns the word "sampled". if it is empty it will use .findnext to find the next value and keep looping till all have been searched. i wrote a subroutine, tested it and it works. but when i use it in a UDF it sets the .findnext to Nothing instead of the next entry. the code is:

Function FindIfSampled(ByVal strholeid As String)
Dim objFind As Object
Dim firstAddress As String
Dim intFindRow As Integer
Dim intLastRow As Integer
intLastRow = ThisWorkbook.Sheets("Sampling Work").Range("B65536").End(xlUp).Row

With ThisWorkbook.Sheets("Sampling Work").Range("B3:B" & intLastRow)

    Set objFind = .Find(strholeid, LookIn:=xlValues, LookAt:=xlWhole)
        
        If Not objFind Is Nothing Then
            firstAddress = objFind.Address
            
            Do
                intFindRow = objFind.Row
                If ThisWorkbook.Sheets("Sampling Work").Range("Y" & intFindRow).Value <> "" Then
                    FindIfSampled = "sampled"
                    Exit Do
                Else: FindIfSampled = ""
                End If
                
                Set objFind = .FindNext(objFind)
            Loop While Not objFind Is Nothing And firstAddress <> objFind.Address
        End If
    Set objFind = Nothing
End With
End Function

while testing it sets the objFind = .findnext(objFind) to nothing, even tho there are other entries. then it just stops and returns a #value at the loop.

any ideas?

thanks
luke

 

[shg]

FindNext doesn't work in UDFs. Use Find again instead.

 

[shg]

E.g.,

Function FindIfSampled(ByVal strholeid As String) As String
    Dim rLook       As Range
    Dim rFind       As Range
    Dim sAddr       As String
 
    With ThisWorkbook.Worksheets("Sampling Work")
        Set rLook = .Range("B3", .Cells(Rows.Count, "B").End(xlUp))
        Set rFind = rLook.Find(What:=strholeid, LookIn:=xlValues, LookAt:=xlWhole)
 
        If Not rFind Is Nothing Then
            sAddr = rFind.Address
 
            Do
                If Not IsEmpty(.Cells(rFind.Row, "Y").Value) Then
                    FindIfSampled = "sampled"
                    Exit Do
                End If
 
                Set rFind = rLook.Find(What:=strholeid, After:=rFind)
            Loop While rFind.Address <> sAddr
        End If
    End With
End Function

 

[lokiluke]

cheers, that solved that problem

another problem has arisen tho, as usual.

quite often a user has to insert a line into the spreadsheet, it seems to dificult for them to remember to copy the formulas down when they do this so i have a macro do it automatically when the spreadsheet is opened.

Dim LASTROW As Integer
ThisWorkbook.Sheets("Core to be logged").Select
LASTROW = ThisWorkbook.Sheets("Core to be logged").Range("C65536").End(xlUp).Row
Range("A3").Select
    Selection.AutoFill Destination:=ThisWorkbook.Sheets("Core to be logged").Range("A3:A" & LASTROW), Type:=xlFillValues
Range("B3").Select
    Selection.AutoFill Destination:=ThisWorkbook.Sheets("Core to be logged").Range("B3:B" & LASTROW), Type:=xlFillValues

the macro has worked well when copying the normal formulas in column A. now tho if a line is added, column B (has the FindIfSampled UDF) calculates to #value. if i manualy select a cell in column B then press enter it calculates that cell fine. if i disable the autofill for column B it calculates fine when opened.

any ideas on what causes this?

maybe i should beat the users with a big stick till they learn

 

[lokiluke]

Managed to stumble across the answer by sheer chance :D added the line Application.CalculateFull after the autofill.

cheers