Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
= CHAR(Row() + 61) | Excel Programming | |||
Char(10) or Char(13) in body of email | Excel Programming | |||
FIND 1 char in cell of any 3 char =True | Excel Discussion (Misc queries) | |||
8500 cells with phone number(7 char.), wishing to add area code (10 char.) | Excel Discussion (Misc queries) | |||
How to removed the first three char and last char in XLS | Excel Programming |