CODEWITHSUNDEEP
vbaexcel
Ali Zakeri
Ali Zakeri

Reputation: 57

Excel VBA code to negate values

I'm writing a VBA code in Excel to modify the values of a certain column:

Below is my code, but sadly 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

Upvotes: 3

Views: 1315

Answers (3)

brettdj
brettdj

Reputation: 55682

Directly using EVALUATE

Worksheets("Sheet2").Range("D4:D100") = Evaluate("=IF(LEFT(Sheet2!C4:C100,1)=""D"",-1*(Sheet2!D4:D100),Sheet2!D4:D100)")

Upvotes: 0

user4039065
user4039065

Reputation:

There seems to be a bit of a mash-up of methods and some steps can probably 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 within a specific workspace. Defining the worksheet with a With/End With clause and prefacing all .Cell and .Range references with a period will bestow that worksheet as the parent of the cells. Since you are looping through a For Each, you need only offset one column to the left to access column B from column C. As mentioned in my comment, I'm unclear on whether you are using Debit or D as criteria but this will work for either; the Left function is simply unnecessary if column C only contains D..

Upvotes: 0

user3819867
user3819867

Reputation: 1120

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's "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

Upvotes: 1

Related Questions