I can locate a cell that I want to sum all values to the right. Problem is
the values have h for hours. 4h, 5h, 8h, etc. Here is my cell:

Cells(Rows.Count, "B").End(xlUp).Offset(0, 1).Value = SUM(Left(?:?,1), etc

Big TIA!

-JS

hi JS,

This isn't quite what you asked for but I think it will do (effectively
& eventually) do same thing... It also shows the impact the other users
are exerince.


VBA Code:
--------------------


Sub tester()
Dim LastCellInColB As Range
With ActiveSheet
Set LastCellInColB = .Cells(.Rows.Count, "B").End(xlUp)
With LastCellInColB
Range(LastCellInColB, .End(xlUp)).Replace What:="h", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
.NumberFormat = "0""h"""
.Offset(0, 1).Formula = "=SUM(" & .End(xlUp).Address & ":" & .Address & ")"
With .Offset(0, 1)
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
End With
End With
End With
Set LastCellInColB = Nothing
End Sub

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




hth
Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?u=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=203669

http://www.thecodecage.com/forumz

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

On Tue, 18 May 2010 14:27:01 -0700, DevourU
wrote:

I can locate a cell that I want to sum all values to the right. Problem is
the values have h for hours. 4h, 5h, 8h, etc. Here is my cell:

Cells(Rows.Count, "B").End(xlUp).Offset(0, 1).Value = SUM(Left(?:?,1), etc

Big TIA!

-JS

It's not clear to me exactly what you are summing, but the Val function will
convert the string 2h into the numeric value of 2.
--ron

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

"Ron Rosenfeld" wrote:

On Tue, 18 May 2010 14:27:01 -0700, DevourU
wrote:

I can locate a cell that I want to sum all values to the right. Problem is
the values have h for hours. 4h, 5h, 8h, etc. Here is my cell:

Cells(Rows.Count, "B").End(xlUp).Offset(0, 1).Value = SUM(Left(?:?,1), etc

Big TIA!

-JS

It's not clear to me exactly what you are summing, but the Val function will
convert the string 2h into the numeric value of 2.
--ron
.

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

On Wed, 19 May 2010 07:33:01 -0700, DevourU
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

Dead on. You rock. Thankx for the assist!

-JS

"Ron Rosenfeld" wrote:

On Wed, 19 May 2010 07:33:01 -0700, DevourU
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
.

On Fri, 21 May 2010 12:25:01 -0700, DevourU
wrote:

Dead on. You rock. Thankx for the assist!

-JS

Glad to help. Thanks for the feedback.
--ron