Problem with range in function

[Jan Kronsell]

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

 

[Chip Pearson]

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]

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]

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]

OK. Thanks.

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