Loop Through column and multiply a cell by a number if Condition is met Excel VBA-CodePudding

Output Needed Original TableI need to loop through column A and if the cell contains the numbers 500 or 600 or 700 i need to multiply column F by -1 to make it negative so -500, -600 or -700. Column F being a duplicate of Column A.

Ex Column A3 is 500 and F3 is 500 i want to make F3 -500

'loop through and multiply 500,600,700 by -1
Dim lr As Long, i As Long
lr = Cells(Rows.Count, "a").End(xlUp).Row
For i = 2 To lr
    If UCase(Cells(i, "a")) = "500" Then Cells(i, "f")*(-1)
    If UCase(Cells(i, "a")) = "600" Then Cells(i, "f")*(-1)
    If UCase(Cells(i, "a")) = "700" Then Cells(i, "f")*(-1)
      
Next i

CodePudding user response：

Please, try the next code. It will clone the A:A range, but values of 500, 600, 700 being multiplied with -1:

Private Sub MultiplyMinus1SomeValues()
   Dim astRow As Long
   lastRow = Range("A" & rows.count).End(xlUp).row
   With Range("F2:F" & lastRow)
        .Formula = "=IF(OR(A2=500,A2=600,A2=700),A2*-1,A2)"
        .Value = .Value
   End With
End Sub