Split a claim over two lines?

[TheWennerWoman]

Hello,

We are creating a mileage expenses claim form. In the UK, in a tax year you can claim £0.45 per mile if you use your private car on business use upto 10,000 miles. Every mile you claim after 10,000 is paid at £0.25 per mile.

On the form, we have some code that gets the total miles claimed during the tax year for the user. This goes into cell A4.

The user enters details of their mileage claim in columns A10-F50, miles claimed will go in cells C10 down to C50. There might only be one line, there might be twenty. So, assume that cell A4 contains 9900, if the user enters 101 into cell C10, I'd like a message to pop up saying "your claim is being split". At which point, in this example, cells A10-F10 will contain the claim but miles will be 100. Then an additional line is added, cells A11-F11 will contain the same data but miles claimed will be 1.

Does that make sense?

 

[Sahak]

hi,
Can we say that, any entry in column "C" if its value is more than 100 will be split into two same records from "A-F", but in column "C" the row will have a value of 100 & next row will have entered value - (minus) 100?
In my example below entry in cell "C14" was 122, which is more than 100, therefore inserted a row (row 15) with the same record as in wow 14, but in cell "C14" the value is 100, and in cell "C15" value is 22 (122-100). Am I understanding correctly?
What we are going to do with 9,900 in cell "A4"?

 

[TheWennerWoman]

hi,
Can we say that, any entry in column "C" if its value is more than 100 will be split into two same records from "A-F", but in column "C" the row will have a value of 100 & next row will have entered value - (minus) 100?
In my example below entry in cell "C14" was 122, which is more than 100, therefore inserted a row (row 15) with the same record as in wow 14, but in cell "C14" the value is 100, and in cell "C15" value is 22 (122-100). Am I understanding correctly?
What we are going to do with 9,900 in cell "A4"?
View attachment 80670

Yes you understand perfectly.

A4 is simply the mileage brought forward from the previous claim (cumulative for the tax year), it can remain static. When the user next makes a claim, that cell will contain the new mileage brought forward.

 

[Sahak]

HI TheWennerWoman,

It is possible that will be other better solutions for your task
The code below if the value in column "C" is bigger than 100 will

• split the mile value
• show a message saying "Your claim has been split"
• highlight split rows

Hope this will help

Option Explicit
Sub SplitMile()
    Dim CntRecs As Long
    CntRecs = Application.CountA(Range("A10:A50"))
    Range(Range("C10"), Range("C10").Offset(CntRecs - 1)).Name = "MilesRng"
    Dim MlCell As Range
    For Each MlCell In Range("MilesRng")
        If MlCell.Value > 100 Then
            MlCell.EntireRow.Copy
            MlCell.Offset(1).EntireRow.Insert
            On Error Resume Next
            ActiveSheet.Paste
            MlCell.Offset(1).Value = MlCell.Value - 100
            MlCell.Value = 100
            Application.CutCopyMode = False
            MsgBox "Your claim has been split"
            Dim SplitAdrs
            SplitAdrs = MlCell.Row
         End If
    Next
    On Error Resume Next
    Range(Rows(SplitAdrs), Rows(SplitAdrs + 1)).Select
End Sub

 

[TheWennerWoman]

Works like a charm, many thanks!!

 

[Sahak]

You are very welcome.