Need Help with Current Macro

p45cal · #8 10-28-2018, 04:31 AM

I note that every account name you want to make negative contains either the word Return or Pilferage, so instead of having a list of full account names (which have to be exactly the same as on your sheet) you could look for instances of (say) return and/or pilfer in the account names instead.
In the code below, there's a line:
myWords = Array("returns", "pilfer")
to which you can add other words to find in account names you want to make negative.

Code:

Sub test()
Dim Cell As Range, ws As Worksheet, myWords, cll As Range
myWords = Array("returns", "pilfer")
For Each ws In Worksheets(Array("130R", "135R", "140R"))
  For Each cll In ws.Range("B4:B45").Cells
  If UBound(Filter(Application.IsErr(Application.Search(myWords, cll.Value)), "True", False)) > -1 Then
      For Each Cell In cll.Offset(, 3).Resize(, 12).Cells
        If Cell.Value > 0 Then Cell.Value = Cell.Value * -1
      Next Cell
    End If
  Next cll
Next ws
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro to auto-save pdf using text on page + current date	hysterical.useless	Word VBA	12	02-05-2018 02:17 PM
macro, data import from the ONLY text file in current folder	ue418	Excel Programming	5	10-28-2017 12:52 PM
a macro that can copy data from copy.xls to our current excel macro.xls based on criteria:	udhaya	Excel Programming	1	11-12-2015 10:12 AM
A macro that moves the current paragraph up or down?	New Daddy	Word VBA	2	04-13-2014 02:25 PM
Macro for moving one cell down from current position	Johnny thunder	Word VBA	3	04-07-2011 04:44 PM