Create macro on excel generated reports

[aikiya22]

Hi, excel masters. I don't know much about macros and VBA but I know that this can be done. if someone would be nice to help me, i would be tremendously grateful. Thank you

here's what I want to achieve,

1. I want to eliminate all texts that have "KM", "Litres", "Liters"
2. I want to eliminate all values under route length that is greater than 500
3. I want to have the sum of route length for each device because the current value for each device is not equal to actual sum
4. I want to do all this for every report with different set of data but has the same format

below is the link to the excel file. Thank you in advance




https://www.dropbox.com/s/me1qxkh4yb9wj2l/Travel Sheet Raw.xls?dl=0

 

[BrianJN1]

Unfortunately there appears to be a quota limit against your account and therefore that file is not accessible.
Have you another way to present this?

 

[aikiya22]

https://drive.google.com/open?id=1Rj2jGUsdVFHLngqKuChPvrhiYD83w8FQ

kindly try this. thank you very much

 

[BrianJN1]

I really don't know what your want as your linked sheet only has KMs mentioned down one column, nothing about Litres/Liters.

Maybe you need to redefine what you are seeking.

 

[aikiya22]

Hi, thank you for the response.

I deleted most of the data because im only allowed to upload upto 1mb, the original file has cells that contains "litres" and "liters"

on column e, when you removed the "km" it will become number, when it is already number, i want it to add up and show the total in B326, B675 next to route length. every report has different sets of data, so it will not always be B326 and B675.

on column E, i just want to eliminate all values that is greater than 500. hope this makes it a little bit clear. thanks

 

[Fluff]

Hi, does this what you're after

Sub Drivers()

    Dim Ar As Areas
    Dim Rng As Range
    Dim Repl As Variant
    Dim Valu As Variant
    
    Repl = Array("km", "litres", "liters")
    For Each Valu In Repl
        Columns(5).Replace Valu, "", xlPart, , False, False, False
    Next Valu
    
    Set Ar = Columns(5).SpecialCells(xlConstants).Areas
    For Each Rng In Ar
        If Not Rng.Rows.Count = 1 Then
            With Rng.Offset(1).Resize(Rng.Count - 1)
                .Name = "a"
                Range("a") = Evaluate("if(a>500,0,a)")
                Range("B" & Rng.Offset(Rng.Count + 1).Row) = WorksheetFunction.Sum(.Value)
            End With
        End If
    Next Rng

End Sub