Macro to make mileage you type into a cell automatically return mileage x $.56

[ExcelJacob]

I am making an expense report for my reps. The national sales manager wants it in excel 2013, and he wants one row that I don't know how to do. He wants the rep to be able to type his mileage into a cell, then have that cell automatically multiply the mileage by $.56 and change to show the expense amount. He wants it all to occur in the same cell.

I am thinking I need to do a macro to make a hidden box on top of the actual cell. Then they can double click on this box, type the amount. Then have the formula in the actual cell and connected to the amount in the box. I hope that makes sense because I have no idea where to start with this one.

Example

Expense: Monday Tuesday Wednesday

Mileage 50 mi 55 mi 75 mi
x $.56


He wants the rep to be able to type the above mileage into the cell and have the cell magically change to say:

Expense: Monday Tuesday Wednesday

Mileage $28.00 $30.80 $42.00
x $.56

 

[Smitty]

Welcome to the Board!

You can do it all at once with a Change event:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> MileageRate <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN><br>    <br>    <SPAN style="color:#007F00">'   Set the mileage rate - NOTE: this will be easier to change if it's on the worksheet</SPAN><br>        MileageRate = 0.56<br>    <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("G:G")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>                Target.Value = Target.Value * MileageRate<br>            Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Input the mileage in column G (change as needed), and it will multiply that * .56 and give you the result.

To use the code, right-click on the worksheet tab-->select View Code-->Paste the code in the new window that opens-->Alt+Q will exit you back to Excel.

Note that users will need to have Macros enabled in order for it to work.

Now you look like a rockstar to the boss.

 

[Smitty]

If you PM me your e-mail address I'll send you an Excel expense report that we used for a Fortune 500 company. Just add your company logo and you're done.

 

[ExcelJacob]

Wow! That was cool. I changed column G to row 13, and "Bam!" I'm very pleased. Thank you.