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/showthread.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 <DevourU@discussions.microsoft.com>
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 <DevourU@discussions.microsoft.com>
> 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 <DevourU@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

Dead on. You rock. Thankx for the assist!

-JS

"Ron Rosenfeld" wrote:

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

On Fri, 21 May 2010 12:25:01 -0700, DevourU <DevourU@discussions.microsoft.com>
wrote:

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

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