Prev: UserForm1.ComboBox1 to initialize
Next: Creating a common starting point
From: Dave Peterson on 19 May 2010 12:05

Option Explicit
Sub testme()

Dim myRng As Range
Dim wks As Worksheet
Dim myVal As Double
Dim LastRow as long

Set wks = Worksheets("Sheet1")

With wks

LastRow = .cells(.rows.count,"B").end(xlup).row

Set myRng = .cells(lastrow,"F").resize(1,7)

myVal = .Evaluate("sum(--left(" & myRng.Address _
& ",len(" & myRng.Address & ")-1))")

.cells(lastrow,"B").value = myval

End With

End Sub

(Untested, uncompiled. Watch for typos.)

On 05/18/2010 17:56, Dave Peterson wrote:
> I'm confused about what the ranges are and what should be summed.
>
> But this may get you closer.
>
> I used column B to get the extent of the range. Then I used .offset(0,1)
> to sum the values in column C.
>
>
>
> Option Explicit
> Sub testme()
>
> Dim myRng As Range
> Dim wks As Worksheet
> Dim myVal As Double
>
> Set wks = Worksheets("Sheet1")
>
> With wks
> Set myRng = .Range("B1", .Cells(.Rows.Count, "B").End(xlUp))
>
> myVal = .Evaluate("sum(--left(" & myRng.Offset(0, 1).Address _
> & ",len(" & myRng.Offset(0, 1).Address & ")-1))")
>
>
> End With
>
> End Sub
>
> On 05/18/2010 16:27, DevourU wrote:
>> 4h, 5h, 8h
From: Ron Rosenfeld on 19 May 2010 15:04
On Wed, 19 May 2010 07:33:01 -0700, DevourU <DevourU(a)discussions.microsoft.com>
wrote:

>Thankx for the replies everyone. I want to sum all values in a row. My row is:
>Cells(Rows.Count, "B").End(xlUp).Offset(0, 1).Value =???, but the cells
>contain an h (4h, 5h, 8h, etc.)
>Note: the columns are always the same. F,G,H,I,J,K,L
>I will try your suggestions, and Thankx. Ideas are welcome. :)
>
>-JS

Perhaps something like:

=======================
Option Explicit
Sub SumH()
Dim c As Range, res As Range
Dim RangeToSum As Range
Dim Temp As Double
Set res = Cells(Rows.Count, "B").End(xlUp).Offset(0, 1)
Set RangeToSum = res.Offset(0, 3).Resize(columnsize:=7)
Temp = 0
For Each c In RangeToSum
Temp = Temp + Val(c)
Next c
res.Value = Temp
End Sub
===========================
--ron
From: DevourU on 21 May 2010 15:25
Dead on. You rock. Thankx for the assist!

-JS

"Ron Rosenfeld" wrote:

> On Wed, 19 May 2010 07:33:01 -0700, DevourU <DevourU(a)discussions.microsoft.com>
> wrote:
>
> >Thankx for the replies everyone. I want to sum all values in a row. My row is:
> >Cells(Rows.Count, "B").End(xlUp).Offset(0, 1).Value =???, but the cells
> >contain an h (4h, 5h, 8h, etc.)
> >Note: the columns are always the same. F,G,H,I,J,K,L
> >I will try your suggestions, and Thankx. Ideas are welcome. :)
> >
> >-JS
>
> Perhaps something like:
>
> =======================
> Option Explicit
> Sub SumH()
> Dim c As Range, res As Range
> Dim RangeToSum As Range
> Dim Temp As Double
> Set res = Cells(Rows.Count, "B").End(xlUp).Offset(0, 1)
> Set RangeToSum = res.Offset(0, 3).Resize(columnsize:=7)
> Temp = 0
> For Each c In RangeToSum
> Temp = Temp + Val(c)
> Next c
> res.Value = Temp
> End Sub
> ===========================
> --ron
> .
>
From: Ron Rosenfeld on 21 May 2010 16:11
On Fri, 21 May 2010 12:25:01 -0700, DevourU <DevourU(a)discussions.microsoft.com>
wrote:

>Dead on. You rock. Thankx for the assist!
>
>-JS

Glad to help. Thanks for the feedback.
--ron
First  |  Prev  | 
Pages: 1 2
Prev: UserForm1.ComboBox1 to initialize
Next: Creating a common starting point