Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code
The code below is intended to match two identical blocks of numbers in
range A1:C120 & F1:H120. I any of the rows do not have a maching number then the out put in the array holdNum() must be placed on the worksheet range K1: P(n). I beleive I have almost accomplished the task except that some of the sets are identical and this is not what is wanted. Can some one please modify so that no dupicates are placed in the output area. Thanks Sub MixNumbers() Dim holdNum(1 To 6) As Integer Dim NumCount As Long Dim r As Long Dim T As Long Dim n As Range Dim S As Range Dim d As Integer Dim v As Variant Dim x As Variant Dim y As Range Dim m As Long Dim z As Integer Dim rn As Long Dim c As Long rn = 1 c = 0 NumCount = 0 'Clear the target area Range("Y2:Y5,S2:X2,K1:P65500").ClearContents Application.StatusBar = "" For T = 1 To 120 For r = 1 To 120 For Each n In Range(Cells(r, 1), Cells(r, 3)) For Each S In Range(Cells(T, 6), Cells(T, 8)) If S = n Then c = c + 1 End If Next S Next n If c = 0 Then For Each v In Union(Range(Cells(T, 6), Cells(T, 8)), Range(Cells(r, 1), Cells(r, 3))) d = d + 1 holdNum(d) = v Next v d = 0 'Temporarily place values in array on worksheet Range(Cells(2, 17), Cells(2, 22)).Value = holdNum() 'Check existing combination to see if there are any matches For m = 1 To NumCount For Each y In Range(Cells(m, 11), Cells(m, 16)) For Each x In holdNum() If y = x Then z = z + 1 End If ' A exact match is found If z = 4 Then z = 0 Exit For Else End If Next x Next y Next m 'Place value of array to target area on worksheet If z < 4 Then Range(Cells(rn, 11), Cells(rn, 16)).Value = holdNum() NumCount = NumCount + 1 Application.StatusBar = "Combinations Processed: " & Format(NumCount, ("#,##0#")) rn = rn + 1 End If End If ' IF C = 0 c = 0 Next r Next T 'application.StatusBar = "" End Sub Thanks for your help. Derick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
option buttons run Click code when value is changed via VBA code | Excel Programming | |||
VBA code delete code but ask for password and unlock VBA protection | Excel Programming |