Help with VBA and SUM

  • Thread starter Thread starter DevourU
  • Start date Start date
D

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
 
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
 
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
Click to expand...

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
 
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.)
 
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
Click to expand...

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 said:
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
.
Click to expand...
 
Back
Top