Almost got it!!!
Everything working in putting the formulae in the right locations and
works as desired if the column is numeric.
The hiccup is the column that uses the Alt+Enter trick to wrap awards on successive lines in a cell has to be text so when write the summation
formula for the group in the summary row for the particular fund it is interpreted as the text string of the sum.
Can manually edit the string and then resave and Excel will then convert
the cell and the sum is computed -- so the Q? now is,
How to automate that process to not have to do that every time update
the sheet?
The formula is, something like
=SUM(N14:N16)
where each of N14 thru N16 are
=sumSplitData(N14)
Public Function SumSplitData(strText)
Dim re As Object
Dim ptrn, Tokens
Dim i As Integer
Dim SumSplitData As Double
Set re = CreateObject("vbscript.regexp")
' allow separators of semicolon, comma, blank, \n
ptrn = "[^;, " + Chr(10) + "]+"
re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True
Set Tokens = re.Execute(strText)
SumSplitData = 0
For i = 0 To Tokens.Count - 1
SumSplitData = SumSplitData + CDbl(Tokens(i))
Next
End Function
If enter any values into A1 and A2, as (say)
A1: 600, <Alt-Enter> 236.54
A2: 1030.01, , <Alt-Enter> 200
A3: =SUM(A1:A2)
and B1, B2 contain =SumSplitData(A1), =SumSplitData(A2), then when programatically via COM write "=SUM(B1:B2)" into B3, the formula in B3
is still text in the Excel spreadsheet while similarly haven written A3, since column A is numeric instead and B is text, A3 is interpreted as
the formula and shows the number while B3 just shows the text.
Hi,
Am Thu, 17 Sep 2020 12:01:51 -0500 schrieb dpb:
Almost got it!!!
Everything working in putting the formulae in the right locations and
works as desired if the column is numeric.
The hiccup is the column that uses the Alt+Enter trick to wrap awards on
successive lines in a cell has to be text so when write the summation
formula for the group in the summary row for the particular fund it is
interpreted as the text string of the sum.
Can manually edit the string and then resave and Excel will then convert
the cell and the sum is computed -- so the Q? now is,
How to automate that process to not have to do that every time update
the sheet?
The formula is, something like
=SUM(N14:N16)
where each of N14 thru N16 are
=sumSplitData(N14)
I have to guess because I don't have your data and your function.
Try it in the cell with:
=--(sumSplitData(N14))
Hi again,
Am Thu, 17 Sep 2020 22:11:07 +0200 schrieb Claus Busch:
try:
Function SplitData(strText As Range) As Double
Dim varTmp As Variant
Dim i As Integer
varTmp = Split(strText, Chr(10))
For i = LBound(varTmp) To UBound(varTmp)
If IsNumeric(Right(varTmp(i), 1)) Then
SplitData = SplitData + CDbl(varTmp(i))
Else
SplitData = SplitData + _
CDbl(Left(varTmp(i), Len(varTmp(i)) - 1))
End If
Next
End Function
no matter what separators you have, you can search for integers and
decimals:
Public Function SumSplitData(strText As String) As Double
Dim re As Object
Dim ptrn, Tokens
Dim i As Integer
Set re = CreateObject("vbscript.regexp")
'looking for integers and decimals
ptrn = "\d+\.?\d*"
re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True
Set Tokens = re.Execute(strText)
SumSplitData = 0
For i = 0 To Tokens.Count - 1
SumSplitData = SumSplitData + CDbl(Tokens(i))
Next
End Function
Regards
Claus B.
try:
Function SplitData(strText As Range) As Double
Dim varTmp As Variant
Dim i As Integer
varTmp = Split(strText, Chr(10))
For i = LBound(varTmp) To UBound(varTmp)
If IsNumeric(Right(varTmp(i), 1)) Then
SplitData = SplitData + CDbl(varTmp(i))
Else
SplitData = SplitData + _
CDbl(Left(varTmp(i), Len(varTmp(i)) - 1))
End If
Next
End Function
Almost got it!!!...
Everything working in putting the formulae in the right locations and
works as desired if the column is numeric.
The hiccup is the column that uses the Alt+Enter trick to wrap awards on successive lines in a cell has to be text so when write the summation
formula for the group in the summary row for the particular fund it is interpreted as the text string of the sum.
Can manually edit the string and then resave and Excel will then convert
the cell and the sum is computed -- so the Q? now is,
How to automate that process to not have to do that every time update
the sheet?
Actually, don't even have to make any changes, just "F2 Enter" for each
cell will convert. If could do that programmatically would seem to
solve the problem...
Hi,
Am Thu, 17 Sep 2020 17:49:19 -0500 schrieb dpb:
Actually, don't even have to make any changes, just "F2 Enter" for each
cell will convert. If could do that programmatically would seem to
solve the problem...
the last posted function is more flexible and more reliable.
And the result of the function is alway a number formatted double.
Have a look:
https://1drv.ms/x/s!AqMiGBK2qniTge8fcVQb86srTXT87g?e=rXZzQW
Almost got it!!!
Everything working in putting the formulae in the right locations and
works as desired if the column is numeric.
The hiccup is the column that uses the Alt+Enter trick to wrap awards on successive lines in a cell has to be text so when write the summation
formula for the group in the summary row for the particular fund it is interpreted as the text string of the sum.
Can manually edit the string and then resave and Excel will then convert
the cell and the sum is computed -- so the Q? now is,
How to automate that process to not have to do that every time update
the sheet?
The formula is, something like
=SUM(N14:N16)
where each of N14 thru N16 are
=sumSplitData(N14)
etc., which is the routine you helped with earlier to split the cell
data by row and total...
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 293 |
Nodes: | 16 (2 / 14) |
Uptime: | 222:12:36 |
Calls: | 6,623 |
Calls today: | 5 |
Files: | 12,171 |
Messages: | 5,318,201 |