#1   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default SUM Char

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


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default SUM Char

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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default SUM Char

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
  #4   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default SUM Char

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


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default SUM Char

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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default SUM Char

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
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default SUM Char

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


  #8   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default SUM Char

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


  #9   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default SUM Char

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


  #10   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default SUM Char

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
= CHAR(Row() + 61) Howard Excel Programming 2 December 25th 12 02:17 AM
Char(10) or Char(13) in body of email ryguy7272 Excel Programming 2 November 12th 08 09:56 PM
FIND 1 char in cell of any 3 char =True Nastech Excel Discussion (Misc queries) 5 April 26th 08 02:17 PM
8500 cells with phone number(7 char.), wishing to add area code (10 char.) [email protected] Excel Discussion (Misc queries) 6 March 10th 06 06:13 PM
How to removed the first three char and last char in XLS Lillian Excel Programming 1 December 21st 04 01:34 AM


All times are GMT +1. The time now is 09:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"