PDA

View Full Version : Solved: How to subtract cell values



twelvety
03-11-2009, 09:52 AM
Hi experts,

I have a worksheet of values and I need to replace each cell with it's original value minus the value of the cell one down and to the right. Can you supply the code to do this?

Thanks in advance,

Chris

Bob Phillips
03-11-2009, 09:57 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim LastRow As Long
Dim i As Long

With Worksheets("Sheet1")

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

With .Cells(i, TEST_COLUMN)

.Value = .Value - .Offset(1, 10).Value
End With
Next i
End With

End Sub

Kenneth Hobs
03-11-2009, 09:58 AM
On a backup copy of the workbook, select your cells and play this from a Module:
Sub Minus11()
Dim c As Range
For Each c In Selection
c.Value = c.Value - c.Offset(1, 1).Value
Next c
End Sub

twelvety
03-11-2009, 10:14 AM
xld, you have said change to suit. What sort of thing would I change this to and what is it that I need to suit?

Sorry for being such a newb!

Bob Phillips
03-11-2009, 10:15 AM
It assumes that the data you want o update is in column A. If it isn't change tat to the appropriate column letter.

twelvety
03-11-2009, 10:19 AM
I want to update all of the data in the cells from A1 to IO186 so quite a lot. Is this code able to be adapted to do that?

Bob Phillips
03-11-2009, 10:27 AM
Won't that be using values it will later update?



Public Sub ProcessData()
Dim cell As Range

With Worksheets("Sheet1")

For Each cell In range("A1:IO186")

With cell

.Value = .Value - .Offset(1, 10).Value
End With
Next i
End With

End Sub

twelvety
03-11-2009, 10:33 AM
I want it to use values it will update later, but not use values which are updated. This code gives me a compile error for next i. Invalid Next control variable reference.

Bob Phillips
03-11-2009, 11:08 AM
Typo



Public Sub ProcessData()
Dim cell As Range

With Worksheets("Sheet1")

For Each cell In range("A1:IO186")

With cell

.Value = .Value - .Offset(1, 10).Value
End With
Next cell
End With

End Sub

twelvety
03-11-2009, 05:14 PM
This is brilliant, how could it be made so that if the value returned is negative it just becomes a zero?

Bob Phillips
03-11-2009, 05:30 PM
Public Sub ProcessData()
Dim cell As Range

With Worksheets("Sheet1")

For Each cell In range("A1:IO186")

With cell

.Value = .Value - .Offset(1, 1).Value
If .Value < 0 Then .Value = 0
End With
Next cell
End With

End Sub

Chris Bode
03-11-2009, 09:59 PM
Use following macro code


Private Sub MySub()
Dim row As Integer, col As Integer
row = 1
col = 1

Dim subt As Double
subt = 0

For row = 1 To 10
subt = CDbl(Sheet1.Cells(row, col).Value) - CDbl(Sheet1.Cells(row + 1, col + 1).Value)
Sheet1.Cells(row, col).Value = subt
Next
End Sub

twelvety
03-12-2009, 04:46 AM
Thanks xld, as usual spot on!