Hi

In column I have:
a
b
c
d

In cell on bottom I want to be "abcd".

when I insert a row with char "s":
a
b
s
c
d
on bottom I want to be "abscd" without change in formula (as =sum(A1:A5)
for number).

Any idea

alpha

Hi,

Am Tue, 6 Aug 2013 13:22:09 +0200 schrieb Alpha:

In column I have:
a
b
c
d

In cell on bottom I want to be "abcd".

do it with a UDF:

Function myChars(myRange As Range) As String
Dim rngC As Range
For Each rngC In myRange
myChars = myChars & rngC
Next
End Function

Call this UDF in the sheet with:
=myChars(A1:A4)


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Function myChars(myRange As Range) As String
Dim rngC As Range
For Each rngC In myRange
myChars = myChars & rngC
Next
End Function

Call this UDF in the sheet with:
=myChars(A1:A4)


Regards
Claus B.
--

It's work
Thanks

Claus,
I didn't see your reply in the other forum the OP cross-posted to, so I
answered it there!

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Tue, 06 Aug 2013 11:46:28 -0400 schrieb GS:

I didn't see your reply in the other forum the OP cross-posted to, so I
answered it there!

one of us is misunderstanding the OP. I thought he wants a string as
result.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Garry,

Am Tue, 06 Aug 2013 11:46:28 -0400 schrieb GS:

I didn't see your reply in the other forum the OP cross-posted to, so I
answered it there!

one of us is misunderstanding the OP. I thought he wants a string as
result.


Regards
Claus B.

My misunderstanding! You are correct...

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Select A5 and define a local scope name as follws...

Name: 'sheet1'!LastCell
RefersTo: A4

...where 'sheet1' is the name of the sheet the name is defined on. Note
there are no $ symbols in the RefersTo value. This makes the ref fully
relative to the cell formula using the name.

Now you can use it like so...

=SUM($a$1:LastCell)

Note that using local scope (sheet level) allows reusing the name on
other sheets without conflict!

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Note that my suggestion only works for numeric value. What you are
trying to do isn't a SUM function, which is what I focussed on. It's
actually a CONCATENATE function. Claus's suggestion of using a UDF is
the way to go in this case...

=myChars($A$1:LastCell)

...so the result auto-updates after insert/delete values from A1 to the
cell above the formula cell.

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Correction...

Note that my suggestion only works for numeric value. What you are trying to
do isn't a SUM function, which is what I focussed on. It's actually a
CONCATENATE function. Claus's suggestion of using a UDF is the way to go in
this case...

=myChars($A$1:LastCell)

..so the result auto-updates after insert/delete values between A1 and
the formula cell.

To include insert/delete at A1, omit the $ symbols in the formula!

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

hi alpha,

=fConcatenate($A$1:$A$4)

Function fConcatenate(rng As Range) As String
Dim c As Range
For Each c In rng
fConcatenate = fConcatenate & c.Value
Next
End Function

isabelle


Le 2013-08-06 07:22, Alpha a écrit :
Hi

In column I have:
a
b
c
d

In cell on bottom I want to be "abcd".

when I insert a row with char "s":
a
b
s
c
d
on bottom I want to be "abscd" without change in formula (as
=sum(A1:A5) for number).

Any idea

alpha