Excel VBA code to reverse values
I am writing VBA code in Excel to change the values of a specific column:
- If cell C (i) is "Debit", multiply the amount in cell B (i) by -1
- otherwise do nothing!
Below is my code, but unfortunately it doesn't work :(
Private Sub Calc()
For Each transType In Worksheets("Sheet2").Range("C4", "C100")
myRow = transType.Row
oldAmount = Worksheets("Sheet2").Range("B" & myRow)
If transType.Value = "D" Then
newAmount.Value = oldAmount.Value * -1
Else:
newAmount = oldAmount
End If
Cells(myRow, "B").Value = newAmount
Next transType
End Sub
source to share
Private Sub Calc()
Dim transType As Range, oldAmount as range 'important
For Each transType In Worksheets("Sheet2").Range("C4", "C100")
myRow = transType.Row
If transType.Value Like "D*" Then 'if it "D" something, e.g. "D" or "Deb" or "Debit"
Worksheets("Sheet2").Range("B" & myRow).Value = Worksheets("Sheet2").Range("B" & myRow).Value * -1
End If
'Cells(myRow, "B").Value = newAmount 'this won't work, it asks for index, the index for column B is 2
Next transType
End Sub
source to share
It looks like there is a bit of blurring of the methods and some steps may be removed.
Private Sub Calc()
Dim transType As Range
With Worksheets("Sheet2")
For Each transType In .Range("C4").Resize(97, 1)
If Left(transType.Value, 1) = "D" Then _
transType.Offset(0, -1) = transType.Offset(0, -1).Value * -1
Next transType
End With
End Sub
Try to work in a specific work area. Defining a worksheet with a With / End With clause and preprocessing all references .Cell
and .Range
with a period will give this worksheet to the parent elements. Since you are stepping through For Each
, you only need to offset one column from the left to access column B from column C. As mentioned in my comment, I don’t understand if you are using the Debit or D criteria, but this will work for both; the function is Left
simply not needed if column C only contains D ..
source to share