Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding all divisors of given number in a set
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding all divisors of given number in a set
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. -- Gary''s Student - gsnu200774 "Mac" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding all divisors of given number in a set
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 -- HTH... Jim Thomlinson "Mac" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to calculate divisors of a number | Excel Discussion (Misc queries) | |||
return in fractions with multiple divisors, linear | Excel Worksheet Functions | |||
Finding Number Within Range Then Copying Data Below Number to Cells | Excel Programming | |||
finding a number and the number of times it occurs | Excel Discussion (Misc queries) | |||
Finding MAX number (excluding a particular number) | Excel Programming |