Help with VBA and SUM

[DevourU]

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

 

[broro183]

hi JS,

This isn't quite what you asked for but I think it will do (effectivel
& eventually) do same thing... It also shows the impact the other user
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

 

[Dave Peterson]

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

 

[Ron Rosenfeld]

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

 

[DevourU]

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

 

[Dave Peterson]

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.)

 

[Ron Rosenfeld]

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

 

[DevourU]

Dead on. You rock. Thankx for the assist!

-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
.

 

[Ron Rosenfeld]

Dead on. You rock. Thankx for the assist!

-JS

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