Sub Permutations()

ActiveSheet.Unprotect
Application.ScreenUpdating = False


Dim arr(0 To 9) As Long
Dim arr1(1 To 3) As Long
Dim cnt As Long, varr As Variant
Dim j As Long, i As Long, k As Long
Dim m As Long, temp As Long
Dim sType As String, j1 as Long
Dim rng As Range, rng1 As Range
'
' set sType to "A" or "B" or "C"
'
sType = UCase(InputBox("Enter A, B or C"))
If sType = "" Then sType = "A"

varr = Range("C15:L15")
arr1(1) = Range("M6").Value
j = 75
col = 4 ' <== 4 is leftmost column for data
' in this case, column D
j1 = j
Cells(j,col).Resize(150, 3).ClearContents
For i = 1 To 1023
bldArr i, arr, cnt
If cnt = 2 Then

m = 2
For k = 0 To 9
If arr(k) = 1 Then
If varr(1, k + 1) = 0 Then
Exit For
End If
arr1(m) = varr(1, k + 1)
m = m + 1
End If
If m = 4 Then Exit For
Next
If m = 4 Then
Cells(j, col).Resize(1, 3).Value = _
Arrtype(arr1, sType)
j = j + 1
temp = arr1(2)
arr1(2) = arr1(3)
arr1(3) = temp
Cells(j, col).Resize(1, 3).Value = _
Arrtype(arr1, sType)
j = j + 1
End If
End If
Next
Set rng = Cells(Rows.Count, col).End(xlUp)
Set rng1 = Range(cells(j1,col), rng).Resize(, 3)
rng1.Resize(, 3).Sort _
Key1:=rng1(1), Order1:=xlDescending, _
Key2:=rng1(1, 2), Order2:=xlDescending, _
Key3:=rng1(1, 3), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:= _
xlTopToBottom

Application.ScreenUpdating = True
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

End Sub

Function Arrtype(ar As Variant, _
ltr As String) As Variant
Dim temp As Long
Dim arr As Variant
arr = ar
Select Case UCase(Left(ltr, 1))
Case "A"

Case "B"
temp = arr(1)
arr(1) = arr(2)
arr(2) = temp
Case "C"
temp = arr(3)
arr(3) = arr(1)
arr(1) = temp
End Select
Arrtype = arr
End Function


Sub bldArr(num As Long, arr() As Long, _
cnt As Long)
Dim lNum As Long, i As Long
lNum = num
Dim sStr As String
sStr = ""
cnt = 0
For i = 9 To 0 Step -1
If lNum And 2 ^ i Then
cnt = cnt + 1
arr(9 - i) = 1
sStr = sStr & "1"
Else
arr(9 - i) = 0
sStr = sStr & "0"
End If
Next
End Sub


--
Regards,
Tom Ogilvy

Richard wrote in message
...
Hello Dave,

Many thanks for your prompt response and concise
explanation to my problem.
You were spot on. The check merged cells was ticked and it
would appear this was causing the problem. Also your macro
to format the combo box cell worked fine. Thanks for all
that.

Dave, could you help me with another problem. Below is a
macro that was working fine until I reorganised my
spreadsheet and changed cell references in my macro. I've
stuffed something up and I don't know what. I have no
backup of my original code as I had a major computer
failure whilst doing this stuff and I've lost track of it.

The macro just gets a common number in cell M6 and
together with numbers in cells C15 to L15, lists them in
three separate columns starting in D75, E75 and F75
downwards. Usually goes to about row 175 but can go
further as more combinations are needed. The user
selection of of A B or C determines which column has the
common number.
Example:-
ColD ColE ColF
SelA SelB SelC
Row75 9 4 6 4 9 6 4 6 9
Row76 9 3 6 3 9 6 3 6 9
Row77 9 2 6 2 9 6 2 6 9

It is crashing at the line at the botton of the macro
starting with... Range("D75").CurrentRegion.Sort to x
1Topto Bottom. The message is:- Error 1004 This operation
requires the merged cells to be identically sized. The
macro is putting the data in rows C D and E which are the
columns I had originally but I have changed the code to be
D E and F, which is where I want my data entered now.

When I enter B or C, columns A and B are being deleted and
I have vital data in them. Can't see how it is doing this.

Could you have a look at it if you have the time. I've got
something tangled up.

Thanks for your help.
Regards,
Richard

Code:

'A M6 value is on the left
'B M6 value is in the middle
'C M6 value is on the right

Option Explicit

Sub Permutations()

ActiveSheet.Unprotect
Application.ScreenUpdating = False


Dim arr(0 To 9) As Long
Dim arr1(1 To 3) As Long
Dim cnt As Long, varr As Variant
Dim j As Long, i As Long, k As Long
Dim m As Long, temp As Long
Dim sType As String

'
' set sType to "A" or "B" or "C"
'
sType = InputBox("Enter A, B or C")

varr = Range("C15:L15")
arr1(1) = Range("M6").Value
j = 75
Range("D75").CurrentRegion.ClearContents
For i = 1 To 1023
bldArr i, arr, cnt
If cnt = 2 Then

m = 2
For k = 0 To 9
If arr(k) = 1 Then
If varr(1, k + 1) = 0 Then
Exit For
End If
arr1(m) = varr(1, k + 1)
m = m + 1
End If
If m = 4 Then Exit For
Next
If m = 4 Then
Cells(j, 3).Resize(1, 3).Value = _
Arrtype(arr1, sType)
j = j + 1
temp = arr1(2)
arr1(2) = arr1(3)
arr1(3) = temp
Cells(j, 3).Resize(1, 3).Value = _
Arrtype(arr1, sType)
' Crashing here
j = j + 1
End If
End If
Next
Range("D75").CurrentRegion.Sort _ 'Crashing here & next 6
Key1:=Range("D75"), Order1:=xlDescending, _
Key2:=Range("E75"), Order2:=xlDescending, _
Key3:=Range("F75"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:= _
xlTopToBottom 'Crashing here

Application.ScreenUpdating = True
ActiveSheet.Protect DrawingObjects:=True,
Contents:=True, Scenarios _
:=True

End Sub

-----Original Message-----
#1. I've never had any problem just selecting the rows
(and just those rows)
that I need to adjust. Are you selecting more than you
should? (same for
columns).

#2. It sounds like you merged the cells, too.
select that range f70:h70
format|cells|Alignment tab|uncheck merged cells

#3. I put a combobox from the controltoolbox toolbar on
my worksheet.
I doubleclicked on it and saw the spot where I could
add this little macro:

Option Explicit
Private Sub ComboBox1_Change()
Me.ComboBox1.Value = Format
(Me.ComboBox1.Value, "00.00")
End Sub

(And I had to go into design mode first (an icon on that
controltoolbox
toolbar).)

(by the way, the dropdown from the Forms toolbar kept the
format of the input
range--I didn't have to do anything special.)

Richard wrote:

Hello,

I've upgraded from Excelv7 for win95 to Excel97 and
would
appreciate if anyone could help with these questions.
I'm
finding it difficult to now do some things that were
second nature to me in the previous version. It's very
frustrating.

1 How do I increase or decrease column or row sizes
several at a time. I could do it in 95 but when I do it
in
97, ALL my previously sized rows or columns revert to
the
one new size. I've had to resort to resizing one at a
time. Surely this can be done in a similar way to
before.
I highlight all the rows I want to change the height for
and select the size, but all my other rows change to
this
value and I have to go back and redo them all again.
Must
be doing something dumb.

2 I've used the Fill color to format my cells F70 to
H70.
I then centered the text across the rows just like I
used
to do. Now I can't delete the formatting of these cells.
In fact I can't put the cursor on G or H70. How do I
reclaim these cells? If I try to copy another cell
over, I
get a message about different sizes and shapes.

3 I have a combo box on my spreadsheet, which I use to
pull in times in the format of 12.30. My list is
formatted
as two decimal places and my target cell is also
formatted
in two decimal places. However, when I click the drop
down
arrow, I can see all the times formatted as two decimal
places but after I click on the desired one it is
displayed as 12.3. I can't get it to display the two
decimal places. I can format the cell fonts but there is
nothing I can see about decimal places. How do I do
this?

Apologies if these are elementary questions but I'm
stumped.

Please!

Regards

--

Dave Peterson

.