Factors of a number - UDF issue

Juggler_IN

Active Member
Joined
Nov 19, 2014
Messages
262
Platform
  1. Windows
I have a UDF to find the Highest Factors of a Number. It is a UDF with array output With most numbers it works fine, for example with input of 24 it outputs 8, 3, NA; with input of 16 it outputs, 4, 4, NA; but with input of 25 it outputs 5, 5, 5, 5, ... in as many cells selected for array output. I have not been able to fix. Any possible solution?
VBA Code:
Public Function Factors( _
       ByVal x As Variant) As Variant

    Dim i As Long, j As Long, obj As Object, var() As Variant

    Set obj = CreateObject("Scripting.Dictionary")
    For i = 2# To (x / 2#)
        j = x Mod i
        If j = 0# Then
            obj.Add i, i
        End If
    Next i
    var = obj.Items()

    Select Case UBound(var)

    Case 0#, 1#
        Factors = var

    Case Is >= 2#
        Factors = Array(var(UBound(var) - 1#), var(LBound(var) + 1#))

    End Select

End Function
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What would you like it to return when there is only one factor?
 

Juggler_IN

Active Member
Joined
Nov 19, 2014
Messages
262
Platform
  1. Windows
@RoryA; Just the factor. So, for input 25, the output to be 5, NA, NA, ...
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You could fudge it by just returning an #NA error as an additional value:

Code:
        Factors = Array(var(0), CVErr(xlErrNA))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top