Hi Dan,
Thanks for your reply. I've downloaded the HTML add-in but I don't know how to use it yet.
My problem is driving me crazy....I've been on it for days. The frustrating part is that if I break my code up and run in two parts separately, the whole thing works BUT only if I enter part A's output manually. If I let the code pick up the output from part A, the results are corrupted. I've assumed it's because I'm picking up data from blank cells, hence my post, but maybe I'm wrong.
Perhaps if I go back to the start and give you all my details you may have a look at it and see where the code is breaking down.
My Problem:
Enter a column of numbers. Range is B8:B31.
In columns C8:E31, select a mixture of those numbers using tick boxes.
The numbers will be random initially but part A of the code puts them together in ascending order.
Use code to work out all the permutations of those selected numbers and output them to Range G9:I100+. (generally won't be more than 100).
Exclude combinations of the same number in the output range.
No blanks i.e. 1 blank 4 or 1,1,4 are invalid.
Solution:
Below is the code in two sections.
The first copies the ticked selections and sorts them in ascending order and outputs them to AE8:AG8 down. They are all integers by the way. Works fine.
The next part of the code computes all possible combinations and outputs the result in range G9:I9 down.
The part not working.
Part 2 of the code BUT.....
If I manually overwrite all the numbers generated by part A in the range AE8:AG8 down and then clear the contents of all the non visible cells (they appear blank) in these 3 rows down to row 31 and then run part B of the code, it runs perfectly and computes all permutations correctly.
Must be something obvious in the code I am overlooking.
Can you help me resolve this please Dan. I'm totally frustrated.
B8:B31 contains integers.
This is the formula in C8 copied down ....
=IF(AND(AB8=TRUE,B8<>""),B8,"")
This is the formula in D8 copied down ....
=IF(AND(AC8=TRUE,B8<>""),B8,"")
This is the formula in E8 copied down ....
=IF(AND(AD8=TRUE,B8<>""),B8,"")
Code Part A
Sub CopySels4Sorting()
Range("F8").Select 'Needed to exit from box mode
Application.ScreenUpdating = False
Range("C8:E31").Select
Selection.Copy
Range("AE8").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("AE8:AE31").Select
Selection.Sort Key1:=Range("AE8"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("AF8:AF31").Select
Selection.Sort Key1:=Range("AF8"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("AG8:AG31").Select
Selection.Sort Key1:=Range("AG8"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("F8").Select
Application.ScreenUpdating = True
End Sub
Code Part B
Sub Compute()
Dim lPtr As Long
Dim lPtr1 As Long
Dim RangeA As Range
Dim RangeB As Range
Dim RangeC As Range
Dim A As Range
Dim B As Range
Dim C As Range
Application.ScreenUpdating = False
Range("G9:I100").Select
Selection.ClearContents
lPtr = Range("AE65536").End(xlUp).Row 'Input Range this macro
Set RangeA = Range("AE8", Cells(lPtr, 31))
lPtr = Range("AF65536").End(xlUp).Row
Set RangeB = Range("AF8", Cells(lPtr, 32))
lPtr = Range("AG65536").End(xlUp).Row
Set RangeC = Range("AG8", Cells(lPtr, 33))
lPtr1 = 9 'Start at line G9
For Each A In RangeA
For Each B In RangeB
For Each C In RangeC
If (A.Value <> B.Value) _
And (A.Value <> C.Value) _
And (B.Value <> C.Value) Then
Cells(lPtr1, 7).Value = A.Value '7=G
Cells(lPtr1, 8).Value = B.Value '8=H
Cells(lPtr1, 9).Value = C.Value '9=I
lPtr1 = lPtr1 + 1
End If
Next C
Next B
Next A
Range("F8").Select
Application.ScreenUpdating = True
End Sub
Both part A and B of the code will eventually be combined to form one macro. Just broke it up here to demonstrate the problem.