How do I work on data starting to a cell a cell with a required va
 

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

How do I work on data starting to a cell a cell with a required va
 

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

How do I work on data starting to a cell a cell with a required va
 

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

How do I work on data starting to a cell a cell with a require
 

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

How do I work on data starting to a cell a cell with a require
 

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

How do I work on data starting to a cell a cell with a require
 

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

How do I work on data starting to a cell a cell with a require
 

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