Problem with range in function

  • Thread starter Thread starter Jan Kronsell
  • Start date Start date
J

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
 
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
 
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
 
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
 
OK. Thanks.

Jan

Chip Pearson said:
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
Click to expand...
 
Back
Top