Sum of Len(string) for an entire row
 

Having a hard time wrapping my head around this one as Im still rusty on
the actual VB functions specific to excel...

Im looking for vb code that will return the sum of all characters in a
particular row, where the column header row (row 1) is not empty...

Thanks in advance!


--
durex
------------------------------------------------------------------------
durex's Profile: http://www.excelforum.com/member.php...o&userid=27857
View this thread: http://www.excelforum.com/showthread...hreadid=478407

Sum of Len(string) for an entire row
 

Hello Durex,

Here is a Worksheet Formula Example to sum everything in row 2...

=SUM("2:2")

Example of summing multiple rows. Rows 2 through 10...

=SUM("2:10")

Example of summing a Range...

=SUM("A2:R240")


Hope this helps,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=47840

Sum of Len(string) for an entire row
 

Actually, to do what Im looking to do using an excel formula, you nee
to use an array formula..

i.e.
For Summing all characters for row 2, columns A2 through G2...
{=SUM(LEN(A2:G2))}

But, Im not looking for the excel formula... Im looking how to do thi
in vbcode.

Thanks for the suggestion

--
dure
-----------------------------------------------------------------------
durex's Profile: http://www.excelforum.com/member.php...fo&userid=2785
View this thread: http://www.excelforum.com/showthread.php?threadid=47840

Sum of Len(string) for an entire row
 

try this
Range("h2").FormulaArray = "=sum(len(A2:g2))"

--


Gary


"durex" wrote in
message ...

Actually, to do what Im looking to do using an excel formula, you need
to use an array formula..

i.e.
For Summing all characters for row 2, columns A2 through G2...
{=SUM(LEN(A2:G2))}

But, Im not looking for the excel formula... Im looking how to do this
in vbcode.

Thanks for the suggestion!


--
durex
------------------------------------------------------------------------
durex's Profile:
http://www.excelforum.com/member.php...o&userid=27857
View this thread: http://www.excelforum.com/showthread...hreadid=478407

Sum of Len(string) for an entire row
 

Hello Durex,

I played it safe answering your first question. Sometimes people say VB
and worksheet formula and vice versa. So, here's what to do.


Code:
--------------------
Public Function AddRow(ByVal Row_Range As String) As Variant

Dim Total
Dim X

For Each X In ActiveSheet.Range(Row_Range)
Total = Total + Val(X.Value)
Next X

AddRow = Total

End Function

--------------------


TO USE IT:

Dim X

X = AddRow("A1:G1")

X = Sum of the row. At least the sum of any valid numbers in the row.


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=478407

Sum of Len(string) for an entire row
 

and if you need the result in a variable, this should do it

TotalLen = Application.Evaluate("=sum(len(a2:g2))")

--


Gary


"durex" wrote in
message ...

Actually, to do what Im looking to do using an excel formula, you need
to use an array formula..

i.e.
For Summing all characters for row 2, columns A2 through G2...
{=SUM(LEN(A2:G2))}

But, Im not looking for the excel formula... Im looking how to do this
in vbcode.

Thanks for the suggestion!


--
durex
------------------------------------------------------------------------
durex's Profile:
http://www.excelforum.com/member.php...o&userid=27857
View this thread: http://www.excelforum.com/showthread...hreadid=478407

Sum of Len(string) for an entire row
 

On Fri, 21 Oct 2005 18:30:07 -0500, durex
wrote:


Having a hard time wrapping my head around this one as Im still rusty on
the actual VB functions specific to excel...

Im looking for vb code that will return the sum of all characters in a
particular row, where the column header row (row 1) is not empty...

Thanks in advance!

One simple way to do this in code is to just step through each cell. So:

===============
Function LenRow(rg As Range) As Double
Dim c As Range
Dim i As Long

For Each c In rg
LenRow = LenRow + Len(c.Text)
Next c
End Function
================


--ron

Sum of Len(string) for an entire row
 

Excellent! Thanks so much! I was actually looking for the total length
of all strings in a particular row, but that got me in the direction I
needed! Just made a minor modification and it worked like a charm.


Code:
--------------------
Public Function AddRow(ByVal Row_Range As String) As Variant
Dim Total
Dim X
For Each X In ActiveSheet.Range(Row_Range)
Total = Total + Len(X.Value)
Next X
AddRow = Total
End Function
--------------------


Just Changed the "Val" to "Len"

Thanks again for the super fast reply!


--
durex
------------------------------------------------------------------------
durex's Profile: http://www.excelforum.com/member.php...o&userid=27857
View this thread: http://www.excelforum.com/showthread...hreadid=478407