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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.