Could anyone tell me how to format a combo box to display
2 decimal places. I cannot get it to display 12.30 etc. It
leaves off the zero and displays 12.3. Where do I do this.
Thanks.

Assuming a combobox from the control toolbox toolbar

if isnumeric(Combobox1.Value) then
Combobox1.Value = format(cdbl(combobox1.Value),"#.00")
End Sub

You haven't said where the combobox is located or what you are doing/how the
value gets in the combobox, so hard to say what event to use. Personally,
if I was using AddItem, when I added it I would do

combobox1.AddItem Format(vVal,"#.00")

so it is already formatted correctly in the the combobox dropdown.

I suspect you are linking to a worksheet range, however.

--
Regards,
Tom Ogilvy

Rick wrote in message
...
Could anyone tell me how to format a combo box to display
2 decimal places. I cannot get it to display 12.30 etc. It
leaves off the zero and displays 12.3. Where do I do this.
Thanks.

Hi Tom,
Also, I posted the below request on 4th Sept last and you
kindly replied with the code below. It works fine but
progressively drops off entries from the output.


Could you have a look at the code.
If I enter 8 in M10 and
2, 4, 5, 6, 7, 9 in C10, D10, E10, F10, G10, H10
respectively, the code runs without error, but some of the
combinations are missing. For example:-
8,7,2
8,7,4
8,7,5
8,9,2
8,7,6
8,6,2
8,6,5
8,6,4
8,9,4 etc etc

This is what I get
8 7 9 Missing 4 combinations
8 6 9
8 6 7 Missing 3
8 5 9
8 5 7
8 5 6 Missing 2
8 4 9
8 4 7
8 4 6
8 4 5 Missing 1
8 2 9
8 2 7
8 2 6
8 2 5
8 2 4 Correct


Also, what change do I need to do to the code to have the
value in M10 appear in the second column only with all the
combinations and also to have the value in M10 appear
exclusively in the third column with all the other
combinations. I will use 3 separate macros.

Cheers
Rick


-----Original Message-----
Option Explicit

Sub tester9()
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
'Dim sStr as String
varr = Range("C10:L10")
arr1(1) = Range("M10").Value
j = 70
Range("C70").CurrentRegion.ClearContents
For i = 1 To 1023
bldArr i, arr, cnt
If cnt = 2 Then

m = 2
' sStr = ""
For k = 0 To 9
' sStr = sStr & arr(k)
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
' Debug.Print i, sStr, m
If m = 4 Then
Cells(j, 3).Resize(1, 3).Value = arr1
j = j + 1
End If
End If
Next

End Sub

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
' If cnt = 2 Then
' Debug.Print num, sStr
' End If
End Sub

You say 8,7,2 is missing, but it isn't 8,2,7 is produced. You must not
understand the mathematical concept of combinations. Combinations don't
assign any special significance to order - so 8,7,2 is the same as 8,2,7 or
2,7,8, etc.

to the best of my knowledge, the code produces the unique combinations. If
you want permutations, then that is different - but you asked for
combinations.

--
Regards,
Tom Ogilvy

"Rick" wrote in message
...
Hi Tom,
Also, I posted the below request on 4th Sept last and you
kindly replied with the code below. It works fine but
progressively drops off entries from the output.


Could you have a look at the code.
If I enter 8 in M10 and
2, 4, 5, 6, 7, 9 in C10, D10, E10, F10, G10, H10
respectively, the code runs without error, but some of the
combinations are missing. For example:-
8,7,2
8,7,4
8,7,5
8,9,2
8,7,6
8,6,2
8,6,5
8,6,4
8,9,4 etc etc

This is what I get
8 7 9 Missing 4 combinations
8 6 9
8 6 7 Missing 3
8 5 9
8 5 7
8 5 6 Missing 2
8 4 9
8 4 7
8 4 6
8 4 5 Missing 1
8 2 9
8 2 7
8 2 6
8 2 5
8 2 4 Correct


Also, what change do I need to do to the code to have the
value in M10 appear in the second column only with all the
combinations and also to have the value in M10 appear
exclusively in the third column with all the other
combinations. I will use 3 separate macros.

Cheers
Rick


-----Original Message-----
Option Explicit

Sub tester9()
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
'Dim sStr as String
varr = Range("C10:L10")
arr1(1) = Range("M10").Value
j = 70
Range("C70").CurrentRegion.ClearContents
For i = 1 To 1023
bldArr i, arr, cnt
If cnt = 2 Then

m = 2
' sStr = ""
For k = 0 To 9
' sStr = sStr & arr(k)
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
' Debug.Print i, sStr, m
If m = 4 Then
Cells(j, 3).Resize(1, 3).Value = arr1
j = j + 1
End If
End If
Next

End Sub

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
' If cnt = 2 Then
' Debug.Print num, sStr
' End If
End Sub

break your links and use code for maximum control of formatting.

--
Regards,
Tom Ogilvy


"Rick" wrote in message
...
Re my other reply post. I forgot to say that I use a macro
to copy the data in cell O8 to another spot (E50:E63) on
my spreadsheet and then count the entries. My count
formula no longer counts the entries in the cells, just
gives a count as zero. The data in the E cells is 1
decimal place.

-----Original Message-----
Assuming a combobox from the control toolbox toolbar

if isnumeric(Combobox1.Value) then
Combobox1.Value = format(cdbl(combobox1.Value),"#.00")
End Sub

You haven't said where the combobox is located or what
you are doing/how the
value gets in the combobox, so hard to say what event to
use. Personally,
if I was using AddItem, when I added it I would do

combobox1.AddItem Format(vVal,"#.00")

so it is already formatted correctly in the the combobox
dropdown.

I suspect you are linking to a worksheet range, however.

--
Regards,
Tom Ogilvy

Rick wrote in message
...
Could anyone tell me how to format a combo box to
display
2 decimal places. I cannot get it to display 12.30 etc.
It
leaves off the zero and displays 12.3. Where do I do
this.
Thanks.


.

Certainly didn't understand the difference between the
two, Tom. Below is what I'm trying to get to. If thats
called permutations then that is what I'm after. Just like
this below. Could you assist.

The reason I need them like this is that they refer to
specific options such as OptionA OptionB OptionC
I need 3 macros to do this as they are unique (dare I say
it, combinations). The next macro would produce as below
except the 8 would be the common number in the middle e.g.
7 8 9 and 7 8 6 and 7 8 5. The last macro would be 7 9 8
and 7 6 8 and 7 5 8 etc.
Hope I haven't confused the issue again.

Obviously the greater the number (up to 10 can be used
with the common number), the larger the permutations. If
you could help I would be grateful.
Thanks.

8 7 9
8 7 6
8 7 5
8 7 4
8 7 2
8 6 9
8 6 7
8 6 5
8 6 4
8 6 2
8 5 9
8 5 7
8 5 6
8 5 4
8 5 2
8 4 9
8 4 7
8 4 6
8 4 5
8 4 2
8 2 9
8 2 7
8 2 6
8 2 5
8 2 4

-----Original Message-----
You say 8,7,2 is missing, but it isn't 8,2,7 is
produced. You must not
understand the mathematical concept of combinations.
Combinations don't
assign any special significance to order - so 8,7,2 is
the same as 8,2,7 or
2,7,8, etc.

to the best of my knowledge, the code produces the unique
combinations. If
you want permutations, then that is different - but you
asked for
combinations.

--
Regards,
Tom Ogilvy

"Rick" wrote in message
...
Hi Tom,
Also, I posted the below request on 4th Sept last and
you
kindly replied with the code below. It works fine but
progressively drops off entries from the output.


Could you have a look at the code.
If I enter 8 in M10 and
2, 4, 5, 6, 7, 9 in C10, D10, E10, F10, G10, H10
respectively, the code runs without error, but some of
the
combinations are missing. For example:-
8,7,2
8,7,4
8,7,5
8,9,2
8,7,6
8,6,2
8,6,5
8,6,4
8,9,4 etc etc

This is what I get
8 7 9 Missing 4 combinations
8 6 9
8 6 7 Missing 3
8 5 9
8 5 7
8 5 6 Missing 2
8 4 9
8 4 7
8 4 6
8 4 5 Missing 1
8 2 9
8 2 7
8 2 6
8 2 5
8 2 4 Correct


Also, what change do I need to do to the code to have
the
value in M10 appear in the second column only with all
the
combinations and also to have the value in M10 appear
exclusively in the third column with all the other
combinations. I will use 3 separate macros.

Cheers
Rick


-----Original Message-----
Option Explicit

Sub tester9()
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
'Dim sStr as String
varr = Range("C10:L10")
arr1(1) = Range("M10").Value
j = 70
Range("C70").CurrentRegion.ClearContents
For i = 1 To 1023
bldArr i, arr, cnt
If cnt = 2 Then

m = 2
' sStr = ""
For k = 0 To 9
' sStr = sStr & arr(k)
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
' Debug.Print i, sStr, m
If m = 4 Then
Cells(j, 3).Resize(1, 3).Value = arr1
j = j + 1
End If
End If
Next

End Sub

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
' If cnt = 2 Then
' Debug.Print num, sStr
' End If
End Sub

















.

You left out
8 9 7
8 9 6
8 9 5
8 9 4
8 9 2

Anyway, this macro will do all three types. set the variable sType equal to
"A", "B", "C"

"A" m10 value is on the left
"B" M10 value is in the middle
"C" M10 value is on the right

Option Explicit

Sub tester9()
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 = "A"
varr = Range("C10:L10")
arr1(1) = Range("M10").Value
j = 70
Range("C70").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)
j = j + 1
End If
End If
Next
Range("C70").CurrentRegion.Sort _
Key1:=Range("C70"), Order1:=xlDescending, _
Key2:=Range("D70"), Order2:=xlDescending, _
Key3:=Range("E70"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:= _
xlTopToBottom
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

Rick wrote in message
...
Certainly didn't understand the difference between the
two, Tom. Below is what I'm trying to get to. If thats
called permutations then that is what I'm after. Just like
this below. Could you assist.

The reason I need them like this is that they refer to
specific options such as OptionA OptionB OptionC
I need 3 macros to do this as they are unique (dare I say
it, combinations). The next macro would produce as below
except the 8 would be the common number in the middle e.g.
7 8 9 and 7 8 6 and 7 8 5. The last macro would be 7 9 8
and 7 6 8 and 7 5 8 etc.
Hope I haven't confused the issue again.

Obviously the greater the number (up to 10 can be used
with the common number), the larger the permutations. If
you could help I would be grateful.
Thanks.

8 7 9
8 7 6
8 7 5
8 7 4
8 7 2
8 6 9
8 6 7
8 6 5
8 6 4
8 6 2
8 5 9
8 5 7
8 5 6
8 5 4
8 5 2
8 4 9
8 4 7
8 4 6
8 4 5
8 4 2
8 2 9
8 2 7
8 2 6
8 2 5
8 2 4

-----Original Message-----
You say 8,7,2 is missing, but it isn't 8,2,7 is
produced. You must not
understand the mathematical concept of combinations.
Combinations don't
assign any special significance to order - so 8,7,2 is
the same as 8,2,7 or
2,7,8, etc.

to the best of my knowledge, the code produces the unique
combinations. If
you want permutations, then that is different - but you
asked for
combinations.

--
Regards,
Tom Ogilvy

"Rick" wrote in message
...
Hi Tom,
Also, I posted the below request on 4th Sept last and
you
kindly replied with the code below. It works fine but
progressively drops off entries from the output.


Could you have a look at the code.
If I enter 8 in M10 and
2, 4, 5, 6, 7, 9 in C10, D10, E10, F10, G10, H10
respectively, the code runs without error, but some of
the
combinations are missing. For example:-
8,7,2
8,7,4
8,7,5
8,9,2
8,7,6
8,6,2
8,6,5
8,6,4
8,9,4 etc etc

This is what I get
8 7 9 Missing 4 combinations
8 6 9
8 6 7 Missing 3
8 5 9
8 5 7
8 5 6 Missing 2
8 4 9
8 4 7
8 4 6
8 4 5 Missing 1
8 2 9
8 2 7
8 2 6
8 2 5
8 2 4 Correct


Also, what change do I need to do to the code to have
the
value in M10 appear in the second column only with all
the
combinations and also to have the value in M10 appear
exclusively in the third column with all the other
combinations. I will use 3 separate macros.

Cheers
Rick


-----Original Message-----
Option Explicit

Sub tester9()
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
'Dim sStr as String
varr = Range("C10:L10")
arr1(1) = Range("M10").Value
j = 70
Range("C70").CurrentRegion.ClearContents
For i = 1 To 1023
bldArr i, arr, cnt
If cnt = 2 Then

m = 2
' sStr = ""
For k = 0 To 9
' sStr = sStr & arr(k)
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
' Debug.Print i, sStr, m
If m = 4 Then
Cells(j, 3).Resize(1, 3).Value = arr1
j = j + 1
End If
End If
Next

End Sub

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
' If cnt = 2 Then
' Debug.Print num, sStr
' End If
End Sub

















.