I have a workbook full of data. I want to sum the data to the left of cells
containing 0. The 0 occurs randomly. Any solutions appreciated but am
learning VBA at the moment and would particularly appreciate a VBA solution.

Thanks

Try this small macro:

Sub sum_to_a_zero()
Dim v
Dim i As Long
i = InputBox("enter row number: ")
For j = 1 To 256
v = v + Cells(i, j).Value
If Cells(i, j).Value = 0 Then
Exit For
End If
Next
MsgBox (v)
End Sub
--
Gary's Student


"Tom London" wrote:

I have a workbook full of data. I want to sum the data to the left of cells
containing 0. The 0 occurs randomly. Any solutions appreciated but am
learning VBA at the moment and would particularly appreciate a VBA solution.

Thanks

I am reading this slightly differently to Gary

Sub SumCells()
Const thisRow As Long = 10 '<=== change to suit
Dim iLastCol As Long
Dim nSum As Double
Dim i As Long

iLastCol = Cells(thisRow, Columns.Count).End(xlToLeft).Column
For i = 2 To iLastCol
If Cells(thisRow, i).Value = 0 And _
Cells(thisRow, i).Value < "" Then
nSum = nSum + Cells(thisRow, i - 1).Value
End If
Next i

MsgBox nSum
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Tom London" <Tom wrote in message
...
I have a workbook full of data. I want to sum the data to the left of
cells
containing 0. The 0 occurs randomly. Any solutions appreciated but am
learning VBA at the moment and would particularly appreciate a VBA
solution.

Thanks

Gary

Thanks that works. I'm going to play around with some of the keywords you
used and figure out how.

I want to sum all rows and display the reslt in the first column. Ant
pointers? I suspect this is fairly easy.

Tom

"Gary''s Student" wrote:

Try this small macro:

Sub sum_to_a_zero()
Dim v
Dim i As Long
i = InputBox("enter row number: ")
For j = 1 To 256
v = v + Cells(i, j).Value
If Cells(i, j).Value = 0 Then
Exit For
End If
Next
MsgBox (v)
End Sub
--
Gary's Student


"Tom London" wrote:

I have a workbook full of data. I want to sum the data to the left of cells
containing 0. The 0 occurs randomly. Any solutions appreciated but am
learning VBA at the moment and would particularly appreciate a VBA solution.

Thanks

Bob

Thanks

The programme appears to be adding the number of occurences in a row of the
0 digit and deducting 1 when I run it. I need to sum all the data to the left
of the 1st (leftmost) 0 in a row and enter that data into column 1.

Thanks again.

Tom

"Bob Phillips" wrote:

I am reading this slightly differently to Gary

Sub SumCells()
Const thisRow As Long = 10 '<=== change to suit
Dim iLastCol As Long
Dim nSum As Double
Dim i As Long

iLastCol = Cells(thisRow, Columns.Count).End(xlToLeft).Column
For i = 2 To iLastCol
If Cells(thisRow, i).Value = 0 And _
Cells(thisRow, i).Value < "" Then
nSum = nSum + Cells(thisRow, i - 1).Value
End If
Next i

MsgBox nSum
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Tom London" <Tom wrote in message
...
I have a workbook full of data. I want to sum the data to the left of
cells
containing 0. The 0 occurs randomly. Any solutions appreciated but am
learning VBA at the moment and would particularly appreciate a VBA
solution.

Thanks

Sub sum_to_a_zero()
Dim iLastRow As Long
Dim iLastcol As Long
Dim nSum As Double
Dim i As Long, j As Long

iLastRow = ActiveSheet.UsedRange.Rows.Count

For i = 1 To iLastRow
nSum = 0
iLastcol = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 2 To iLastcol
nSum = nSum + Cells(i, j).Value
If Cells(i, j).Value = 0 Then
Exit For
End If
Next j
Cells(i, "A").Value = nSum
Next i
End Sub




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Tom London" wrote in message
...
Gary

Thanks that works. I'm going to play around with some of the keywords you
used and figure out how.

I want to sum all rows and display the reslt in the first column. Ant
pointers? I suspect this is fairly easy.

Tom

"Gary''s Student" wrote:

Try this small macro:

Sub sum_to_a_zero()
Dim v
Dim i As Long
i = InputBox("enter row number: ")
For j = 1 To 256
v = v + Cells(i, j).Value
If Cells(i, j).Value = 0 Then
Exit For
End If
Next
MsgBox (v)
End Sub
--
Gary's Student


"Tom London" wrote:

I have a workbook full of data. I want to sum the data to the left of
cells
containing 0. The 0 occurs randomly. Any solutions appreciated but am
learning VBA at the moment and would particularly appreciate a VBA
solution.

Thanks

Bob

That works beautifully. Thank you.

Tom

"Bob Phillips" wrote:


Sub sum_to_a_zero()
Dim iLastRow As Long
Dim iLastcol As Long
Dim nSum As Double
Dim i As Long, j As Long

iLastRow = ActiveSheet.UsedRange.Rows.Count

For i = 1 To iLastRow
nSum = 0
iLastcol = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 2 To iLastcol
nSum = nSum + Cells(i, j).Value
If Cells(i, j).Value = 0 Then
Exit For
End If
Next j
Cells(i, "A").Value = nSum
Next i
End Sub




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Tom London" wrote in message
...
Gary

Thanks that works. I'm going to play around with some of the keywords you
used and figure out how.

I want to sum all rows and display the reslt in the first column. Ant
pointers? I suspect this is fairly easy.

Tom

"Gary''s Student" wrote:

Try this small macro:

Sub sum_to_a_zero()
Dim v
Dim i As Long
i = InputBox("enter row number: ")
For j = 1 To 256
v = v + Cells(i, j).Value
If Cells(i, j).Value = 0 Then
Exit For
End If
Next
MsgBox (v)
End Sub
--
Gary's Student


"Tom London" wrote:

I have a workbook full of data. I want to sum the data to the left of
cells
containing 0. The 0 occurs randomly. Any solutions appreciated but am
learning VBA at the moment and would particularly appreciate a VBA
solution.

Thanks