Code breaks despite using "On error resume next"

[abdulbasitb]

I don't understand why the code below breaks at line 14. (xComp = xCell.Value)
It passes that line once but breaks the second time in the loop.

I'm using it as UDF like this: =GetFirstFactor(A1,B2:B10)
Note: There are some blank cells in the range B2:B10 with the value = ""

Function GetFirstFactor(RefValue As Range, xArr As Range) As Variant
'The purpose of this function is to find the first value in range xArr which is a factor of RefValue.
'eg. If values in xArr = 4,6,8,3,12 and RefValue = 18, then output will be 6.

    Dim xVal As Double, xComp As Double, xCell As Variant
   
    On Error Resume Next
        xVal = RefValue.Cells(1).Value2
    On Error GoTo 0
   
    For Each xCell In xArr.Cells
   
        On Error Resume Next
            xComp = xCell.Value
            Err.Clear
        On Error GoTo 0
       
        On Error GoTo NextRow
        If xVal Mod xComp < 0.001 Then
            GetFirstFactor = xVal
            Exit Function
        End If
NextRow:
    Err.Clear
    On Error GoTo 0
    Next
End Function

 

[abdulbasitb]

Note: The cell value B3 = ""
The code is meant to ignore such cells and move on.

 

[Zot]

Is using xCell as Variant makes a difference?

 

[abdulbasitb]

Is using xCell as Variant makes a difference?

Thanks Zot for prompt response.

In the original code above, I'm using xCell as a Variant.
I've also tried xCell as Range and also tried not declaring it.

None of these work.

 

[Zot]

Thanks Zot for prompt response.

In the original code above, I'm using xCell as a Variant.
I've also tried xCell as Range and also tried not declaring it.

None of these work.

On final On Error Goto NextRow, you have Err.Clear. What happened when no error? Code goes through On Error Goto NextRow and then Exit Function. The error is still flagged.

I think that is the problem. Before Exit Function, try add On Error GoTo 0.

 

[dmt32]

Hi,
untested but see if these changes to your function resolve your issue

Function GetFirstFactor(ByVal RefValue As Range, ByVal xArr As Range) As Variant
    'The purpose of this function is to find the first value in range xArr which is a factor of RefValue.
    'eg. If values in xArr = 4,6,8,3,12 and RefValue = 18, then output will be 6.
    
    Dim xVal        As Double, xComp As Double
    Dim xCell       As Range
    
    With RefValue.Cells(1)
        If Not IsNumeric(.Value2) Then Exit Function
        xVal = .Value2
    End With
    
    For Each xCell In xArr.Cells
        On Error GoTo NextRow
        If IsNumeric(xCell.Value) Then
            xComp = xCell.Value
            If xVal Mod xComp < 0.001 Then
                GetFirstFactor = xVal
                Exit Function
            End If
        End If
NextRow:
        Err.Clear
    Next
        
End Function

Dave

 

[abdulbasitb]

Thanks dmt32, that worked!
FYI Zot, tried the solution in second message but didn't work.
Thanks for suggestions.

 

[Zot]

Thanks dmt32, that worked!
FYI Zot, tried the solution in second message but didn't work.
Thanks for suggestions.

Forget my suggestion. There is no logic to it. The blood flow in my brain was reversed for no apparent reason ?