Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / March 2008

Tip: Looking for answers? Try searching our database.

Finding all divisors of given number in a set

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mac - 18 Mar 2008 16:37 GMT
Hello,

is there an algorithm for this:
In a column, say A, I have some 150 numbers; then I have one number N and I
need to find all divisors of number N in column A and have them ''extracted'
to a list, or marked, or whatever. Is there a piece of code? Thank you!
Mac
Gary''s Student - 18 Mar 2008 17:22 GMT
VBA not needed, just use autofilter.  Say C3 contains  123456
and A2 thru A30 has:

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

In B2 enter:

=MOD($C$2,A2) and copy down.  A1 thru B30 will show:

asd    gfd
2    0
3    0
4    0
5    1
6    0
7    4
8    0
9    3
10    6
11    3
12    0
13    8
14    4
15    6
16    0
17    2
18    12
19    13
20    16
21    18
22    14
23    15
24    0
25    6
26    8
27    12
28    4
29    3
30    6

then set the autofilter on column B for display 0:

asd    gfd
2    0
3    0
4    0
6    0
8    0
12    0
16    0
24    0
32    0
48    0
64    0
96    0

In your case you will have 150 possibilities rather than 30.

Signature

Gary''s Student - gsnu200774

> Hello,
>
[quoted text clipped - 3 lines]
> to a list, or marked, or whatever. Is there a piece of code? Thank you!
> Mac
Jim Thomlinson - 18 Mar 2008 17:34 GMT
You could try this... I have assumed that the list is on sheet1 and that N is
in Cell B1 on that sheet. Change to suit.

You could also use Conditional Formatting without any code to just highlight
the values if you wanted.

Sub Divisors()
   Dim wksToSearch As Worksheet
   Dim rngToSearch As Range
   Dim lngNumerator As Long
   Dim rng As Range
   Dim wksNew As Worksheet
   Dim rngPaste As Range
       
   Set wksNew = Worksheets.Add
   On Error Resume Next
   wksNew.Name = "Denominators" 'try to rename sheet
   On Error GoTo 0
   Set rngPaste = wksNew.Range("A1")
   
   Set wksToSearch = Sheets("Sheet1")
   With wksToSearch
   Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
   End With
   
   lngNumerator = wksToSearch.Range("B1").Value
   
   For Each rng In rngToSearch
       If lngNumerator Mod rng.Value = 0 Then
           rngPaste.Value = rng.Value
           Set rngPaste = rngPaste.Offset(1, 0)
       End If
   Next rng    
End Sub
Signature

HTH...

Jim Thomlinson

> Hello,
>
[quoted text clipped - 3 lines]
> to a list, or marked, or whatever. Is there a piece of code? Thank you!
> Mac
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.