Challenge: a macro to fix incorrect values in a spreadsheet

[acantor]

I track my expenses in an Excel spreadsheet. For most expenses, I pay 13% in taxes. For example, if the pre-tax value for an item is $100, I pay a total of $113.

 

I set up my spreadsheet to accept pre-tax values. The spreadsheet uses formulas to calculate the amount of tax I paid on an item, and then sums the two:

Pre-tax            Tax         Total

100.00             13.00       $113.00

10.00               1.30         $11.30

1.00                 0.13         $1.13

 

Unfortunately, I accidentally inputted hundreds of expenses before realizing that I had inserted totals instead of the pre-tax amounts.

 

The challenge: Write a macro to change the value of a cell from the post-tax amount to the pre-tax amount.

 

For example, the macro will change a value of "113" incorrectly entered into in the Pre-Tax column to "100."

 

Assume the cells that you want to correct only contain numbers: no text, and no formulas.

 

This is a real challenge. I actually made this mistake last week. My macro took 10 or 15 minutes to create, and saved hours of tedium to repair.

 

Bonus: Because there might be hundreds of these mistakes in the spreadsheet, anything your solution does to speed up the process of repairing values will earn you brownie points!

[Cory]

I wouldn't use a macro. I'd create a parallel column and have Excel do the calculation. Then copy that column and paste it back over the old column with the "Paste as values" option. 

[acantor]

Hi Cory,

 

For the sake of this challenge, assume that some cells need to be repaired, and some don't; and that the cells in need of repair are not necessarily contiguous.

 

In other words, the repair cannot be applied to an entire column of cells. The repair must be applied to individual cells based on other criteria, e.g., expenses that were entered into the spreadsheet on certain days, or whatever.

[terrypin]

Hi Alan,

 

I don’t see the challenge in this one?

 

Y = 1.13 * X, so X = Y/1.13, for the selected X that the user recognises as a mistake.

 

A bit of rounding and that’s it, yes?

 

Terry

[acantor]

Hi Terry,

 

This challenge is not nearly as complex as others that have appeared in this forum, but at the same time, it's not quite as straightforward as it seems.

 

For example, there are many ways to retrieve the contents of a cell so the value can be passed to the macro. I'm aware of three methods. But the methods are not equally reliable. When I tried to "translate" Method 1 into Macro Express code, the script got convoluted. Method 2 failed.

 

Method 3 seems to maximize reliability: So far, it works regardless of the initial state of a cell: whether it has keyboard focus ("Ready mode"), is in "Edit mode", or in "End mode."

[terrypin]

Hi Alan,

 

In garden using iPad so can’t try it yet. But with the target cell selected I would expect a copy followed by set variable then a trim would deliver the text you need for the rest. Failing that, an F2, a move to the edit box, and take the copy from there.

 

But a VBA macro would be my personal choice! Not only because it would be so much faster but it would be a refresher exercise as I haven’t done any VBA stuff for ages.

 

Terry

[acantor]

Terry, your last response has led me to the conclusion that I was overthinking this!

 

Quote

But with the target cell selected I would expect a copy followed by set variable ...

 

I tried three different ways to select the contents of a cell, all of which involved Edit mode. After reading your message, I realized there was a fourth way: forget about Edit mode. Simply copy!

 

And with that realization, the only fail-safe needed is to make sure the cell is NOT in Edit mode. (In Edit mode, content must be selected before copying.) Pressing <ESC> exits Edit mode, and does nothing in Ready or End mode.

 

// Cancel "Edit" mode (does nothing if not in Edit mode)
Text Type (Simulate Keystrokes): <ESC>
 
// Copy to the clipboard
Text Type (Simulate Keystrokes): <CONTROL>c
Delay: 40 milliseconds // 30 ms is too short of a delay
 
// We're dealing with dollars and cents, so use a Decimal variable
Variable Set Decimal %ClipD% from the clipboard contents
 
// Calculate the correct value (13% less than current value) and round to two decimal places
Variable Modify Decimal: %PreTax% = %ClipD% / 1.13
Variable Modify Decimal: Round %PreTax% to 2 decimal places
 
// Output the result
Text Type (Simulate Keystrokes): %PreTax%
 
// Press <TAB> to move to the cell to the right, or <ENTER> to move to the cell below
Text Type (Simulate Keystrokes): <TAB>

<COMMENT Value="Cancel \"Edit\" mode (does nothing if not in Edit mode)"/>
<TEXT TYPE Action="0" Text="<ESC>"/>
<COMMENT/>
<COMMENT Value="Copy to the clipboard"/>
<TEXT TYPE Action="0" Text="<CONTROL>c"/>
<DELAY Flags="\x02" Time="40" _COMMENT="30 ms is too short of a delay"/>
<COMMENT/>
<COMMENT Value="We're dealing with dollars and cents, so use a Decimal variable"/>
<VARIABLE SET DECIMAL Option="\x02" Destination="%ClipD%"/>
<COMMENT/>
<COMMENT Value="Calculate the correct value (13% less than current value) and round to two decimal places"/>
<VARIABLE MODIFY DECIMAL Option="\x03" Destination="%PreTax%" Value1="%ClipD%" Value2="1.13"/>
<VARIABLE MODIFY DECIMAL Option="\x04" Destination="%PreTax%" Places="2"/>
<COMMENT/>
<COMMENT Value="Output the result"/>
<TEXT TYPE Action="0" Text="%PreTax%"/>
<COMMENT/>
<COMMENT Value="Press <TAB> to move to the cell to the right, or <ENTER> to move to the cell below"/>
<TEXT TYPE Action="0" Text="<TAB>"/>

 

Maybe the next challenge will be more challenging!!

[terrypin]

Hi Alan,

 

My greatest challenge at present is trying to achieve a day’s worth of intended tasks in a 24 hour day!

 

Can hardly believe that I used to manage a commute and work as well as hobbies. 
 

Terry