1 TO 9 EXCEPT CELL CONTENT

C

CHRIS K

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?
 
A

Arvi Laanemets

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.
 
C

CHRIS K

What's a UDF?
--
CHRISK


Arvi Laanemets said:
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.
Click to expand...
 
A

Arvi Laanemets

With workbook open:
press Alt+F11
Insert>Module (when you don't have one)
Insert>Procedure, 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
 
C

CHRIS K

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?
 
A

Arvi Laanemets

Hi

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