CODEWITHSUNDEEP
excelvba
user1941320
user1941320

Reputation: 41

VBA Calculations to add columns based on one columns answer

I have the following code so far to subtract one column from another, but then need to take that result and if D is negative then F = D + F else E = E + D

  Sub PopCol()
      Range("D3:D19").Formula = "=RC[-1]-RC[-2]"  'D=C-B

I'm Lost! Been trying different formats for range in this and have had no luck-HELP!

Thank You!

Upvotes: 2

Views: 220

Answers (2)

user2063626
user2063626

Reputation:

Try below code :

  Sub PopCol()
    For i = 3 To 19
        Range("D" & i) = CLng(Range("C" & i) - Range("B" & i))
        If (Range("D" & i) < 0) Then
            Range("F" & i) = Range("D" & i) + Range("F" & i)
        Else
            Range("E" & i) = Range("E" & i) + Range("D" & i)
        End If
    Next
End Sub

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149287

Is this what you are trying?

Sub PopCol()
    Dim rng As Range, aCell As Range

    Set rng = Range("D3:D19")

    rng.Formula = "=RC[-1]-RC[-2]"

    For Each aCell In rng
        Select Case aCell.Value

        Case Is < 0 '<~~ If value in D is negative
            '~~>  F = F + D
            aCell.Offset(, 2).Value = aCell.Offset(, 2).Value + aCell.Value
        Case Else
            '~~>  E = E + D
            aCell.Offset(, 1).Value = aCell.Offset(, 1).Value + aCell.Value
        End Select
    Next
End Sub

Upvotes: 1

Related Questions