Automated: Inverting a select few values from many

Inverting a select few values from a data sheet of many

Hello,


I have a data sheet that I regularly update with new data, into a report that uses pivot tables and pre-programmed cells to summarize the data.

For an example, lets pretend my data represents the sales of tickets to a theme park, like my favorite - Galactic Studios in Orleans, Florian. Attached is a sample workbook with sample data.
Note: To fully understand my example, it's critical to note that "RAINY DAY" are (in the example) tickets that were refunded because it rained and customers complained. So customer service (Officer No. 99999) issued the complaining customers refunds in the form of new tickets to use another day.
I want to count those tickets as negative because they effectively reduce the number of tickets that contribute to normal 'sales'. I know it might not make perfect sense but try to work with my example haha.

One of the data interpretation routines that I must carry out for the data to be summarized correctly is to select a few rows (from all of the thousands of rows) and invert some values in a few (constant) columns, i.e. turn the value from its positive to the equal but negative value.


When I was doing this manually I discovered that if I tried the most straightforward method of doing this, it resulted in a major error. Here's what I mean by most straightforward:



MANUAL PROCESS (Results in Error):

In sheet1: "Raw Data": Select Data>Filter. Then, Click on filter button on column C header "Admission type (Category)" > Uncheck 'Select All' > Check 'RAINY DAY' & 'COMPLAINT'

Sheet now displays only rows 8,9,34,35.

i) in a random cell, input '-1'. then, select that cell, copy it's value.

ii)Select all the cells (for each row) in column C. Effectively, selected range is "C8:C35"

iii)right click on selected range> Paste > Paste Special > Paste Special > Operation: Multiply > OK

Then after, redo steps i-iii but for each column: D, E.

Error:
Doing this results in Excel also pasting/multiplying by -1 for all the rows inbetween rows 8-35, as demonstrated by sheet 'Actual result (Error)'




So, I devised a (very) manual workaround method to do this:

MANUAL PROCESS (Workaround, results successful)

In sheet1: "Raw Data": Select Data>Filter. Then, Click on filter button on column B header "Admission type (Category)" > Uncheck 'Select All' > Check 'RAINY DAY' & 'COMPLAINT'

Sheet now displays only rows 8,9,34,35.

On a piece of paper, write down the row numbers of the values I need to invert. So, write down: "8,9,34,35".

In sheet1: "Raw Data": Select Data>Filter to remove the filtering.

Then, manually scroll to the rows in question (8,9,34,35) and PasteSpecial > Multiply (-1) to invert the individual cells with values in columns C, D, & E.

So, now I have a manual method that works, if a little inconvenient.





Well, I set out to automate my worksheet with VBA code and Macros, and whadya know, my first attempt of doing this value inversion resulted in the same error. I guess it was long enough ago that I devised the workaround method that I forgot the error it caused.

Here is my current VBA Code: This code works in the sense of it does exactly what I would with the original MANUAL PROCESS (Results in Error):

Public Sub Invert_Refunds()
'
' Invert_Refund Macro
' Invert the refunded tickets due to rainy days into negative values.

'
    Application.CutCopyMode = False
    
    
    ThisWorkbook.Worksheets("RAW DATA").Activate
    ThisWorkbook.Worksheets("RAW DATA").Range("A1").Select
    Selection.AutoFilter
    ThisWorkbook.Worksheets("RAW DATA").Range("$A$1:$F$40").AutoFilter Field:=3, Criteria1:= _
        "RAINY DAY"
    
    
    'Vulcan Bay
    
    ThisWorkbook.Worksheets("RAW DATA").Range("C1").Select
    ActiveCell.Offset(1).Select
    ThisWorkbook.Worksheets("RAW DATA").Range(Selection, Selection.End(xlDown)).Select
    
    ThisWorkbook.Worksheets("CONFIG").Activate
    ThisWorkbook.Worksheets("CONFIG").Range("A1").Copy
    
    ThisWorkbook.Worksheets("RAW DATA").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
    
    
    'Islets of Adventure
    
    ThisWorkbook.Worksheets("RAW DATA").Range("D1").Select
    ActiveCell.Offset(1).Select
    ThisWorkbook.Worksheets("RAW DATA").Range(Selection, Selection.End(xlDown)).Select
    
    ThisWorkbook.Worksheets("CONFIG").Activate
    ThisWorkbook.Worksheets("CONFIG").Range("A1").Copy
    
    ThisWorkbook.Worksheets("RAW DATA").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
    
    
    'Galactic Studios
    
    ThisWorkbook.Worksheets("RAW DATA").Range("E1").Select
    ActiveCell.Offset(1).Select
    ThisWorkbook.Worksheets("RAW DATA").Range(Selection, Selection.End(xlDown)).Select
    
    ThisWorkbook.Worksheets("CONFIG").Activate
    ThisWorkbook.Worksheets("CONFIG").Range("A1").Copy
    
    ThisWorkbook.Worksheets("RAW DATA").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
    
    
    Application.CutCopyMode = False
    
    'Release the AutoFilter to return the data to normal, with inversions complete
    ThisWorkbook.Worksheets("RAW DATA").Range("A1").Select
    Selection.AutoFilter
    
End Sub

Note: I know that if my data wont always be 40 rows long, I need to make code that recognizes that. I will in the future. That's not the primary issue at hand though, and that's something I know how to fix with code.

So, how might I go about reprogramming this to work properly?

Here's what I've thought:

I know that the 'Sales Number' values in the first columns are unique / primary keys;

So I figure I might have to make an array and store all the 'Sales Number' values for the rows with "RAINY DAY" as Admission type (Category), after AutoFiltering by that category description.
Then, Once I have my array with stored sales numbers,
Remove the AutoFilter to display the raw data,
Do a for loop that uses each "Sales Number" value to find a row with that "Sales Number" and then does simple math to change the values in that row's cells in columns C, D & E to negative values,
Like this:

Range("C8") = -1 * Range("C8").Value

until the for loop has gone through the whole array.

Is this the most simple, efficient and effective way to do this?

Or is there other Excel built-in functionality that I'm not taking into consideration, or perhaps a simpler algorithm / function design?

I appreciate all and any help and input.