Prev: UserForm1.ComboBox1 to initialize
Next: Creating a common starting point
From: DevourU on 18 May 2010 17:27
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
From: broro183 on 18 May 2010 18:39

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

From: Dave Peterson on 18 May 2010 18:56
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 18 May 2010 20:14
On Tue, 18 May 2010 14:27:01 -0700, DevourU <DevourU(a)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
From: DevourU on 19 May 2010 10:33
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(a)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
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: UserForm1.ComboBox1 to initialize
Next: Creating a common starting point