CODEWITHSUNDEEP
vbaexcel
Derek
Derek

Reputation: 11

Paste Cell as Negative Value if Criteria is met

I'm trying to put together a macro to copy specific data if the following 2 criteria are met:

Copy column O of that row and paste in worksheet "Input" but as a negative value.

I am currently stuck on how to paste this as a negative value.

Here is my code:

Sub Negative()

    Dim Day As String
    Dim Direction As String
    Dim LastRow As Integer
    Dim i As Integer

    Sheets("Input").Range("B9:D28").ClearContents

    Day = Sheets("Input").Range("B6").Value
    Direction = Sheets("Input").Range("B7").Value

    For i = 2 To LastRow
        If Sheets("Data").Cells(i, 9) = Day Then
            If Sheets("Data").Cells(i, 17) = Direction Then
                Sheets("Data").Range(Sheets("Data").Cells(i, 15), Sheets("Data").Cells(i, 15)).Copy
                Sheets("Input").Range("C29").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
            End If
        End If
    Next i

End Sub

Upvotes: 1

Views: 815

Answers (1)

Calico
Calico

Reputation: 416

Untested, but something like this should work. Just update your central IF statement slightly

If Sheets("Data").Cells(i, 17) = Direction Then
    Sheets("Data").Range(Sheets("Data").Cells(i, 15), Sheets("Data").Cells(i, 15)).Copy
    With Sheets("Input").Range("C29").End(xlUp).Offset(1, 0)
        .PasteSpecial xlPasteFormulasAndNumberFormats
        .Value = .Value * -1
    End With
End If

Upvotes: 1

Related Questions