Annual Accounting Worksheet - Mileage Macro Help Required

[BG0612]

Hi I'm new

This is a query for an expert Macro writer who understands coding.

I'm also new to Macro's and need to know how to set up a macro that can calculate at two different percentage ratios over one worksheet. I can link the results with another workpage using formulaes (so don't need help with that).

I have set up a new accounting spreadsheet and there is a car mileage workbook page, for which I need to enter my daily business and personal mileage. I want the data (daily mileage total entered) to calculate automatically the amount I can claim in government allowances to give a total cost of mileage for any given day. At present, the annual mileage allowance is 0.45 ppm for up to 10,000 miles and 0.25 ppm over 10,000 miles (standard UK flat rate). So when I enter my mileage throughout the year, running the macro, I want the total amount claimable, based on these allowances according to the miles I have entered into the cell, to be accurate whether under or over 10,000 miles. Otherwise, I will have to use a calculator.

Could someone help me with writing the Macro code , and what I need to do to set up the macro in the first place? From looking at another example of a similar problem using different figures unrelated to mine, but still based on mileage, I worked it out as this (with B6 being the entry cell). Please don't ask me to justify what I have written below. It was just guesstimated but it seems right if the source of the information code was correct, as given to the person with a similar problem to mine.

=MAX(B6-10000,0)*25+(MAX(B6-10000,0)-MAX(B6-10000,0))*45

If that is not right (clueless, as I am) could you write the code correctly for me, please? I have no idea what I am doing as I was just following patterns not understanding what I was doing when copying the code. I have no background in VBA or Macros.

Many thanks
BG0612

[Kaper]

But do you really need a macro?

Assuming your mileage is written in column B starting form row 2 (1st row reserved for headers) use in X2 (X being your column with allowance)

Please Login or Register  to view this content.

(you wont go over 10 000 first day ) and such a formula in X3 (and then copy down):

Please Login or Register  to view this content.