VBA If Statement

[Bernice]

I have an Excel spreadsheet that has a type in column B which can be Pay,
Bill or Pen. If it is a Pay in column B, then I want to have cols C- F be
negative value (-1) of what is in the current cell. I would like to create a
macro, since I have about 50,000 rows to go through.

Example: column B - PAY, col C 10.55, col D 5, col E 6, col F 4.55 result
would be
column B - PAY, col C -10.55, col D -5, col E -6 and col F -4.55. If col B
is BIL or PEN then it would be ignored.

Thanks in advance for your assistance

 

[Jacob Skaria]

Try the below macro which works on the active sheet

Sub Macro3()
Dim lngRow As Long
For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If UCase(Range("B" & lngRow)) = "PAY" Then _
For lngCol = 3 To 6: Cells(lngRow, lngCol) = _
Cells(lngRow, lngCol) * -1: Next
Next
End Sub

If this post helps click Yes

 

[Per Jessen]

Hi

Try this:

Sub ConvertPAY()
Dim FirstRow As Long
Dim LastRow As Long
Application.ScreenUpdating = False

LastRow = Range("B" & Rows.Count).End(xlUp).Row
FirstRow = 2 ' Heading in row 1
Range("Q1") = -1 'Change Q1 change to a unused cell
Range("Q1").Copy
For r = FirstRow To LastRow
If Cells(r, "B") = "PAY" Then
Range(Cells(r, "C"), Cells(r, "F")).PasteSpecial _
Operation:=xlPasteSpecialOperationMultiply
End If
Next
Application.CutCopyMode = xlCopy
Application.CutCopyMode = True
End Sub

Regards,
Per