Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL function;find string in entire column & return cell referenc | Excel Worksheet Functions | |||
Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
to search for a string and affect data if it finds the string? | Excel Worksheet Functions | |||
Reads entire *.txt file into string opposed to a desired line by line input. | Excel Programming | |||
Create a formula into a String then assign string to a cell | Excel Programming |