I have this function that works nicely:

Function Saml() As String
Dim Navn As String
Navn = ""
For Each c In ActiveSheet.Range("B1:B100).Cells
If IsEmpty(c.Value) Then Exit For
Navn = Navn & c.Value & ";"
Next c
Saml = Navn
End Function

Now I try to make my Function dynamic by changing it to

Function Saml(Omr As Range) As String
Dim Navn As String
Navn = ""
For Each c In ActiveSheet.Range(Omr).Cells
If IsEmpty(c.Value) Then Exit For
Navn = Navn & c.Value & ";"
Next c
Saml = Navn
End Function

But now it returns a #Value! code in stead of the result. What do I do wrong
here?

Jan

Jan,

Change

For Each c In ActiveSheet.Range(Omr).Cells
' to
For Each c In Omr.Cells


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Jan Kronsell" wrote in
message ...
I have this function that works nicely:

Function Saml() As String
Dim Navn As String
Navn = ""
For Each c In ActiveSheet.Range("B1:B100).Cells
If IsEmpty(c.Value) Then Exit For
Navn = Navn & c.Value & ";"
Next c
Saml = Navn
End Function

Now I try to make my Function dynamic by changing it to

Function Saml(Omr As Range) As String
Dim Navn As String
Navn = ""
For Each c In ActiveSheet.Range(Omr).Cells
If IsEmpty(c.Value) Then Exit For
Navn = Navn & c.Value & ";"
Next c
Saml = Navn
End Function

But now it returns a #Value! code in stead of the result. What
do I do wrong here?

Jan

Thank you very much. That did it :-)

But that creates another question.

All the cells in the ranger contains 8 characters. So for each cell with the
additional ";" it adds up to 9 chars. When my range contains for example
2500 cells, the cell with function contains 22500 chars (using the len()
function), and changing the range to 3000 cells, the lenght is changed to
27000. That is apparently correct, but not all of the characters are
displayed in the cell, only about the first 1024.

How do I make Excel show me all of the characters in the cell?

Jan

"Chip Pearson" skrev i en meddelelse
...
Jan,

Change

For Each c In ActiveSheet.Range(Omr).Cells
' to
For Each c In Omr.Cells


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Jan Kronsell" wrote in message
...
I have this function that works nicely:

Function Saml() As String
Dim Navn As String
Navn = ""
For Each c In ActiveSheet.Range("B1:B100).Cells
If IsEmpty(c.Value) Then Exit For
Navn = Navn & c.Value & ";"
Next c
Saml = Navn
End Function

Now I try to make my Function dynamic by changing it to

Function Saml(Omr As Range) As String
Dim Navn As String
Navn = ""
For Each c In ActiveSheet.Range(Omr).Cells
If IsEmpty(c.Value) Then Exit For
Navn = Navn & c.Value & ";"
Next c
Saml = Navn
End Function

But now it returns a #Value! code in stead of the result. What do I do
wrong here?

Jan

Excel won't display more that 1024 characters in a cell (unless
you toss in a Chr(10) every hundred characters or so), even
though a cell can contain 32K characters.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Jan Kronsell" wrote in
message ...
Thank you very much. That did it :-)

But that creates another question.

All the cells in the ranger contains 8 characters. So for each
cell with the additional ";" it adds up to 9 chars. When my
range contains for example 2500 cells, the cell with function
contains 22500 chars (using the len() function), and changing
the range to 3000 cells, the lenght is changed to 27000. That
is apparently correct, but not all of the characters are
displayed in the cell, only about the first 1024.

How do I make Excel show me all of the characters in the cell?

Jan

"Chip Pearson" skrev i en meddelelse
...
Jan,

Change

For Each c In ActiveSheet.Range(Omr).Cells
' to
For Each c In Omr.Cells


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Jan Kronsell" wrote in
message ...
I have this function that works nicely:

Function Saml() As String
Dim Navn As String
Navn = ""
For Each c In ActiveSheet.Range("B1:B100).Cells
If IsEmpty(c.Value) Then Exit For
Navn = Navn & c.Value & ";"
Next c
Saml = Navn
End Function

Now I try to make my Function dynamic by changing it to

Function Saml(Omr As Range) As String
Dim Navn As String
Navn = ""
For Each c In ActiveSheet.Range(Omr).Cells
If IsEmpty(c.Value) Then Exit For
Navn = Navn & c.Value & ";"
Next c
Saml = Navn
End Function

But now it returns a #Value! code in stead of the result.
What do I do wrong here?

Jan

OK. Thanks.

Jan

"Chip Pearson" skrev i en meddelelse
...
Excel won't display more that 1024 characters in a cell (unless you toss
in a Chr(10) every hundred characters or so), even though a cell can
contain 32K characters.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Jan Kronsell" wrote in message
...
Thank you very much. That did it :-)

But that creates another question.

All the cells in the ranger contains 8 characters. So for each cell with
the additional ";" it adds up to 9 chars. When my range contains for
example 2500 cells, the cell with function contains 22500 chars (using
the len() function), and changing the range to 3000 cells, the lenght is
changed to 27000. That is apparently correct, but not all of the
characters are displayed in the cell, only about the first 1024.

How do I make Excel show me all of the characters in the cell?

Jan

"Chip Pearson" skrev i en meddelelse
...
Jan,

Change

For Each c In ActiveSheet.Range(Omr).Cells
' to
For Each c In Omr.Cells


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Jan Kronsell" wrote in message
...
I have this function that works nicely:

Function Saml() As String
Dim Navn As String
Navn = ""
For Each c In ActiveSheet.Range("B1:B100).Cells
If IsEmpty(c.Value) Then Exit For
Navn = Navn & c.Value & ";"
Next c
Saml = Navn
End Function

Now I try to make my Function dynamic by changing it to

Function Saml(Omr As Range) As String
Dim Navn As String
Navn = ""
For Each c In ActiveSheet.Range(Omr).Cells
If IsEmpty(c.Value) Then Exit For
Navn = Navn & c.Value & ";"
Next c
Saml = Navn
End Function

But now it returns a #Value! code in stead of the result. What do I do
wrong here?

Jan