abdulbasitb
New Member
- Joined
- Apr 21, 2015
- Messages
- 17
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 = ""
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 = ""
VBA Code:
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
Last edited: