Hello,

I'm trying to automate a method of summing across columns. To be
exact, let's say I have 5 columns (A B C D E), the first of which
MIGHT contain a negative value (it may also contain a positive value,
and B C D E might have a zero or some positive value. What I want to
do is... IF A has a negative value I want it to add it to E (to reduce
it), if E becomes zero and there is still some left from A, I want it
to add the remainder to D... then C.... then B. Essentially I want
the values of E D C B to be reduced, in turn, by the value that is in
A (but only if A has a negative value).

I'm hoping this makes sense... and I'm hoping someone out there can
figure this out.

Cheers.

Try this. Select the row in question and run the macro

Public Sub test()
Dim tmp As Double
Dim nAmount As Double
Dim iRow As Long
Dim icol As Long
iRow = ActiveCell.Row
nAmount = Cells(iRow, "A").Value
icol = 5
Do While nAmount < 0 And icol 1
tmp = nAmount
nAmount = nAmount + Cells(iRow, icol).Value
Cells(iRow, icol).Value = Application.Max(0, Cells(iRow, icol).Value
+ tmp)
icol = icol - 1
Loop
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
oups.com...
Hello,

I'm trying to automate a method of summing across columns. To be
exact, let's say I have 5 columns (A B C D E), the first of which
MIGHT contain a negative value (it may also contain a positive value,
and B C D E might have a zero or some positive value. What I want to
do is... IF A has a negative value I want it to add it to E (to reduce
it), if E becomes zero and there is still some left from A, I want it
to add the remainder to D... then C.... then B. Essentially I want
the values of E D C B to be reduced, in turn, by the value that is in
A (but only if A has a negative value).

I'm hoping this makes sense... and I'm hoping someone out there can
figure this out.

Cheers.

Sub SubtractValues()
Dim cell As Range, cell1 As Range
Dim v, v1, i As Long
For Each cell In Range("A1:A20")
v = cell.Value
If IsNumeric(v) Then
If v < 0 Then
For i = 5 To 2 Step -1
Set cell1 = Cells(cell.Row, i)
If IsNumeric(cell1) Then
If v < 0 Then
If cell1 + v = 0 Then
cell1 = cell1 + v
v = 0
Else
v = v + cell1
cell1 = 0
End If
End If
End If
Next i
Cells(cell.Row, 6) = v
End If
End If
Next
End Sub

--
Regards,
Tom Ogilvy

" wrote:

Hello,

I'm trying to automate a method of summing across columns. To be
exact, let's say I have 5 columns (A B C D E), the first of which
MIGHT contain a negative value (it may also contain a positive value,
and B C D E might have a zero or some positive value. What I want to
do is... IF A has a negative value I want it to add it to E (to reduce
it), if E becomes zero and there is still some left from A, I want it
to add the remainder to D... then C.... then B. Essentially I want
the values of E D C B to be reduced, in turn, by the value that is in
A (but only if A has a negative value).

I'm hoping this makes sense... and I'm hoping someone out there can
figure this out.

Cheers.

Hmmm... neither work as expected... but maybe if I give more
details... here is what my spreadsheet looks like:

J K
L M
N O P
(41,328.80) - 11,755.73 17,197.26 5,143.26 12,419.83
-


So what I want to happen is for the amount in J to be added to P to
reduce it to zero which it already is, then the remainder to be added
to O to reduce it to zero... and so on until it gets to L where the
amount would be 5,187.28




On Feb 21, 12:23 pm, Tom Ogilvy
wrote:
Sub SubtractValues()
Dim cell As Range, cell1 As Range
Dim v, v1, i As Long
For Each cell In Range("A1:A20")
v = cell.Value
If IsNumeric(v) Then
If v < 0 Then
For i = 5 To 2 Step -1
Set cell1 = Cells(cell.Row, i)
If IsNumeric(cell1) Then
If v < 0 Then
If cell1 + v = 0 Then
cell1 = cell1 + v
v = 0
Else
v = v + cell1
cell1 = 0
End If
End If
End If
Next i
Cells(cell.Row, 6) = v
End If
End If
Next
End Sub

--
Regards,
Tom Ogilvy



" wrote:
Hello,

I'm trying to automate a method of summing across columns. To be
exact, let's say I have 5 columns (A B C D E), the first of which
MIGHT contain a negative value (it may also contain a positive value,
and B C D E might have a zero or some positive value. What I want to
do is... IF A has a negative value I want it to add it to E (to reduce
it), if E becomes zero and there is still some left from A, I want it
to add the remainder to D... then C.... then B. Essentially I want
the values of E D C B to be reduced, in turn, by the value that is in
A (but only if A has a negative value).

I'm hoping this makes sense... and I'm hoping someone out there can
figure this out.

Cheers.- Hide quoted text -

- Show quoted text -

with your data in J2 to P2, this worked fine for me:

Sub AASubtractValues()
Dim cell As Range, cell1 As Range
Dim v, v1, i As Long
For Each cell In Range("J2:J2")
v = cell.Value
If IsNumeric(v) Then
If v < 0 Then
' P is column 16
' k is column 11
For i = 16 To 11 Step -1
Set cell1 = Cells(cell.Row, i)
If IsNumeric(cell1) Then
If v < 0 Then
If cell1 + v = 0 Then
cell1 = cell1 + v
v = 0
Else
v = v + cell1
cell1 = 0
End If
End If
End If
Next i
End If
End If
Next
End Sub

You specified your data was in columns A to E. therefore, you need to
adjust the macro to work on a different range. So for example, it your
sampel data is in J10, then you would need to change
Range("J2:J2")
to
Range("J10:J10")

as an example. If it were in a different column, you would need to make
adjustments in the for i = x to y step -1

Getting clearer now?

--
Regards,
Tom Ogilvy


" wrote:

Hmmm... neither work as expected... but maybe if I give more
details... here is what my spreadsheet looks like:

J K
L M
N O P
(41,328.80) - 11,755.73 17,197.26 5,143.26 12,419.83
-


So what I want to happen is for the amount in J to be added to P to
reduce it to zero which it already is, then the remainder to be added
to O to reduce it to zero... and so on until it gets to L where the
amount would be 5,187.28




On Feb 21, 12:23 pm, Tom Ogilvy
wrote:
Sub SubtractValues()
Dim cell As Range, cell1 As Range
Dim v, v1, i As Long
For Each cell In Range("A1:A20")
v = cell.Value
If IsNumeric(v) Then
If v < 0 Then
For i = 5 To 2 Step -1
Set cell1 = Cells(cell.Row, i)
If IsNumeric(cell1) Then
If v < 0 Then
If cell1 + v = 0 Then
cell1 = cell1 + v
v = 0
Else
v = v + cell1
cell1 = 0
End If
End If
End If
Next i
Cells(cell.Row, 6) = v
End If
End If
Next
End Sub

--
Regards,
Tom Ogilvy



" wrote:
Hello,

I'm trying to automate a method of summing across columns. To be
exact, let's say I have 5 columns (A B C D E), the first of which
MIGHT contain a negative value (it may also contain a positive value,
and B C D E might have a zero or some positive value. What I want to
do is... IF A has a negative value I want it to add it to E (to reduce
it), if E becomes zero and there is still some left from A, I want it
to add the remainder to D... then C.... then B. Essentially I want
the values of E D C B to be reduced, in turn, by the value that is in
A (but only if A has a negative value).

I'm hoping this makes sense... and I'm hoping someone out there can
figure this out.

Cheers.- Hide quoted text -

- Show quoted text -

And this works for me

Public Sub test()
Const START_COL As Long = 10 'column J change if required
Dim tmp As Double
Dim nAmount As Double
Dim iRow As Long
Dim icol As Long
iRow = ActiveCell.Row
nAmount = Cells(iRow, START_COL).Value
icol = START_COL + 5
Do While nAmount < 0 And icol 1
tmp = nAmount
nAmount = nAmount + Cells(iRow, icol).Value
Cells(iRow, icol).Value = Application.Max(0, Cells(iRow, icol).Value
+ tmp)
icol = icol - 1
Loop
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
oups.com...
Hmmm... neither work as expected... but maybe if I give more
details... here is what my spreadsheet looks like:

J K
L M
N O P
(41,328.80) - 11,755.73 17,197.26 5,143.26 12,419.83
-


So what I want to happen is for the amount in J to be added to P to
reduce it to zero which it already is, then the remainder to be added
to O to reduce it to zero... and so on until it gets to L where the
amount would be 5,187.28




On Feb 21, 12:23 pm, Tom Ogilvy
wrote:
Sub SubtractValues()
Dim cell As Range, cell1 As Range
Dim v, v1, i As Long
For Each cell In Range("A1:A20")
v = cell.Value
If IsNumeric(v) Then
If v < 0 Then
For i = 5 To 2 Step -1
Set cell1 = Cells(cell.Row, i)
If IsNumeric(cell1) Then
If v < 0 Then
If cell1 + v = 0 Then
cell1 = cell1 + v
v = 0
Else
v = v + cell1
cell1 = 0
End If
End If
End If
Next i
Cells(cell.Row, 6) = v
End If
End If
Next
End Sub

--
Regards,
Tom Ogilvy



" wrote:
Hello,

I'm trying to automate a method of summing across columns. To be
exact, let's say I have 5 columns (A B C D E), the first of which
MIGHT contain a negative value (it may also contain a positive value,
and B C D E might have a zero or some positive value. What I want to
do is... IF A has a negative value I want it to add it to E (to reduce
it), if E becomes zero and there is still some left from A, I want it
to add the remainder to D... then C.... then B. Essentially I want
the values of E D C B to be reduced, in turn, by the value that is in
A (but only if A has a negative value).

I'm hoping this makes sense... and I'm hoping someone out there can
figure this out.

Cheers.- Hide quoted text -

- Show quoted text -