Need Help with Current Macro

rsrasc · #1 10-26-2018, 04:08 AM

Hi all,

The following macro was given to me in a previous post and it is working great.

Code:

Sub test()
    Dim Cell As Range
    Dim ws As Worksheet
    For Each ws In Worksheets(Array("130R", "135R", "140R"))
        For Each Cell In ws.Range("E4:P45")
            If Cell.Value > 0 Then
                Cell.Value = Cell.Value * -1
            End If
        Next Cell
    Next ws
End Sub

Now, due to some unexpected changes, I would like to know if the above macro can be modified.

For example the below code will not changed

Code:

For Each ws In Worksheets(Array("130R", "135R", "140R"))

The same as

Code:

For Each Cell In ws.Range("E4:P45")
            If Cell.Value > 0 Then
                Cell.Value = Cell.Value * -1

In my file in column B, from row 4 to 43, I have different account numbers.

For example, some of the accounts listed in Column B are the following:

Circulation-All Other Single-Gross Sales
Circulation-All Other Single-Gross Sales (Sun)
Other Printing Revenue
Other Printing Services-Quick Print

Therefore, since only these accounts needs to be converted to negative, I was wondering how this can be accomplished.

As always, thank you in advance for your assistance and cooperation.

Cheers!
rsrasc

Logit · #2 10-26-2018, 07:29 PM

Code:

Sub test()
    Dim Cell As Range
    Dim ws As Worksheet
    For Each ws In Worksheets(Array("130R", "135R", "140R"))
        For Each Cell In ws.Range("E4:P45")
            If Cell.Value > 0 Then
                Cell.Value = Cell.Value * -1
            End If
        Next Cell

	For Each Cell In ws.Range("B4:B43")
            If Cell.Value > 0 Then
                Cell.Value = Cell.Value * -1
            End If
        Next Cell
    Next ws
End Sub

rsrasc · #3 10-27-2018, 06:02 AM

Thank you Logit for your reply but it did not work.

Let me see if can get a better explanation.

The code given to me in the below code will convert all numbers from positive to negative for any number in the range E4 through P45.

Code:

Sub test()
    Dim Cell As Range
    Dim ws As Worksheet
    For Each ws In Worksheets(Array("130R", "135R", "140R"))
        For Each Cell In ws.Range("E4:P45")
            If Cell.Value > 0 Then
                Cell.Value = Cell.Value * -1
            End If
        Next Cell
    Next ws
End Sub

Since I realized that some of the numbers in the range E4:P45 cannot be converted to negative numbers, my question was if the original macro given to me can be modified.
This is now where a new macro or a modified macro is needed.

New requirement:

The modified macro will still need the following code:

Code:

For Each ws In Worksheets(Array("130R", "135R", "140R"))

Now, under these three (3) sheets, "130R", "135", and "140R", in Column B, I have the following information or accounts with their corresponding values from Column E to Column P:

Local Advertising-Full Run
General Advertising-Full Run
Advertising-Insert-Local-Full Run
Advertising-Classified-Display
Advertising-Classified-Liners
Advertising-Special Supplements
Circulation-AAFES/NEX-Gross Sales
Circulation-AAFES/NEX-Returns
Circulation-AAFES/NEX-Gross Sales (Sun)
Circulation-AAFES/NEX-Returns(Sun)
Circulation-DECA-Gross Sales(M-Sat)
Circulation-DECA-Returns(M-Sat)
Circulation-DECA-Gross Sales(Sun)
Circulation-DECA-Returns (Sun)
Circulation-Home Delivery-Gross Sales
Circulation-Home Delivery-Gross Sales (Sun)
Circulation-All Other Single-Gross Sales
Circulation-All Other Single-Returns
Circulation-All Other Single-Gross Sales (Sun)
Circulation-All Other Single-Returns (Sun)
Circulation-Vending Machines-Gross Sales
Foreign Currency Conversion Gains
Circulation-Vending Machines-Pilferage
Circulation-Vending Machines-Gross Sales (Sun)
Circulation-Vending Machines-Returns (Sun)
Circulation-Vending Machines-Pilferage (Sun)
Circulation-Exercises/Maneuvers-Gross Sales
Circulation-Exercises/Maneuvers-Gross Sales (Sun)
Circulation-Mail Subscriptions-Gross Sales
Circulation-Mail Subscriptions-Gross Sales (Sun)
Circulation-Direct Drops-Gross Sales
Circulation-Direct Drops-Gross Sales (Sun)
Circulation-Bulk Sales-(Daily)
Circulation-Bulk Sales-(Sunday)
Other Printing Revenue
Other Printing Services-Quick Print
Foreign Currency Conversion Gains
Other Publications Sales-Other Publ Delivery
Advertising-Internet Revenue
Other Revenue

Out of the above listing or information in Column B, I would like the macro to only convert the following accounts from positive to negative numbers.

Circulation-AAFES/NEX-Returns
Circulation-AAFES/NEX-Returns(Sun)
Circulation-DECA-Returns(M-Sat)
Circulation-DECA-Returns (Sun)
Circulation-All Other Single-Returns
Circulation-All Other Single-Returns (Sun)
Circulation-Vending Machines-Pilferage
Circulation-Vending Machines-Returns (Sun)
Circulation-Vending Machines-Pilferage (Sun)

Therefore, since these are the only accounts that needs to be converted from positive numbers to negative numbers, then my question is if this is possible.

I hope this clarify what is needed.

Thank you all for your assistance and cooperation.

Regards,
rsrasc

Logit · #4 10-27-2018, 09:10 AM

.
It would be best to post a sample workbook. Leave out any confidential information.

rsrasc · #5 10-27-2018, 10:12 AM

Please find attached sample.

.

p45cal · #6 10-27-2018, 10:19 AM

untested:

Code:

Sub test()
Dim Cell As Range
Dim ws As Worksheet
AcctsToMakeNegative = Array("Circulation-AAFES/NEX-Returns", "Circulation-AAFES/NEX-Returns(Sun)", "Circulation-DECA-Returns(M-Sat)", "Circulation-DECA-Returns (Sun)", "Circulation-All Other Single-Returns", "Circulation-All Other Single-Returns (Sun)", "Circulation-Vending Machines-Pilferage", "Circulation-Vending Machines-Returns (Sun)", "Circulation-Vending Machines-Pilferage (Sun)")
For Each ws In Worksheets(Array("130R", "135R", "140R"))
  For Each cll In ws.Range("B4:B45").Cells
    If Not IsError(Application.Match(cll.Value, AcctsToMakeNegative, 0)) 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

rsrasc · #7 10-27-2018, 02:42 PM

Hi pcal45,

Thank you for the code. Great job.

I'm really happy with the results.

Regards,
rsrasc

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

rsrasc · #9 10-28-2018, 05:41 AM

Hi p45cal,

Your observation is well taken.

I'm sure your macro will help me somehow in the future.

Code:

If Cell.Value > 0 Then Cell.Value = Cell.Value * -1

Since I was playing with the macro, I also realized I can change from negative to positive by inverting the greater (>) than sign to less than sign (<).

You guys are amazing.

Thank you for your support.

Much appreciated

Regards,
rsrasc

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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