1 TO 9 EXCEPT CELL CONTENT
 

if I have the number 5 in cell a1 how can I get 1,2,3,4,6,7,8,9 to appear in
cell c1?
ie 1-9 but not 5
then if there are two numbers in a1 eg 2 and 7 I want 1,3,4,5,6,8,9 to
appear in cell c1
Any suggestions?
--
CHRISK

1 TO 9 EXCEPT CELL CONTENT
 

Hi

=SUBSTITUTE(SUBSTITUTE("1,2,3,4,5,6,7,8,9",A1,""), ",,",",")

But the 2nd part of your question in't so easy to answer anymore. Probably
the best solution will be an UDF.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"CHRIS K" wrote in message
...
if I have the number 5 in cell a1 how can I get 1,2,3,4,6,7,8,9 to appear
in
cell c1?
ie 1-9 but not 5
then if there are two numbers in a1 eg 2 and 7 I want 1,3,4,5,6,8,9 to
appear in cell c1
Any suggestions?
--
CHRISK

1 TO 9 EXCEPT CELL CONTENT
 

What's a UDF?
--
CHRISK


"Arvi Laanemets" wrote:

Hi

=SUBSTITUTE(SUBSTITUTE("1,2,3,4,5,6,7,8,9",A1,""), ",,",",")

But the 2nd part of your question in't so easy to answer anymore. Probably
the best solution will be an UDF.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"CHRIS K" wrote in message
...
if I have the number 5 in cell a1 how can I get 1,2,3,4,6,7,8,9 to appear
in
cell c1?
ie 1-9 but not 5
then if there are two numbers in a1 eg 2 and 7 I want 1,3,4,5,6,8,9 to
appear in cell c1
Any suggestions?
--
CHRISK

1 TO 9 EXCEPT CELL CONTENT
 

Hi


"CHRIS K" wrote in message
...
What's a UDF?


User Defined Function


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )

1 TO 9 EXCEPT CELL CONTENT
 

HOW do you do a UDF?
--
CHRISK


"Arvi Laanemets" wrote:

Hi


"CHRIS K" wrote in message
...
What's a UDF?


User Defined Function


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )

1 TO 9 EXCEPT CELL CONTENT
 

With workbook open:
press Alt+F11
InsertModule (when you don't have one)
InsertProcedure, type in the function name, set Type to Function and press
OK
A dummy function is created. Fill it with code, and close VBA editor.
When you now activate Paste Function wizard in Excel, you can find created
UDF under category User Defined.

Some rules you have to follow:
By definition a function can't change anything - it only returns a value. So
you have to avoid according statements, like Select, Activate, etc.
To return a value, you have to save it to variable with same name as
function.

An example:
Public Sub Test(TestString As String) As String
Test=TestString
End Sub


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"CHRIS K" wrote in message
...
HOW do you do a UDF?
--
CHRISK


"Arvi Laanemets" wrote:

Hi


"CHRIS K" wrote in message
...
What's a UDF?


User Defined Function


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )

1 TO 9 EXCEPT CELL CONTENT
 

Where can I find what "language" to use for the UDF
have NOT got a VBA book.
Is there a web site I can look at that will define what various things do?
--
CHRISK

1 TO 9 EXCEPT CELL CONTENT
 

Hi

For Excel UDF's, you have to use VBA. When in VBA editor, activate Help -
there was enough information to start for me.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


"CHRIS K" wrote in message
...
Where can I find what "language" to use for the UDF
have NOT got a VBA book.
Is there a web site I can look at that will define what various things do?
--
CHRISK