VBA If Statement

  • Thread starter Thread starter Bernice
  • Start date Start date
B

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
 
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
 
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
 
Back
Top