function to sum a set of values (random row count) 2

[collierd]

Hello

Using Excel 2003 I want to create a function (there's more I want to do with this so that's all I need in the first instance)

I have some data on a worksheet that looks like this:

000000999xxx
1 789000abc000001000
1 765000def000000050
1 765000klmn000002050
9990003100

There's always a start (000..) and end (999..) row
These are all single column entries i.e. they occur in Row A
There can be any number of rows between the start and end row
Row 1 is a start reference
Rows that start with 1 are data rows (the text in the middle is a customer id

At the end of each rows the last 6 digits make up a number of miles e.g. 1 789000abc000001000 is 1000 miles

The sum total of this should add up to the last six digits on the last row i.e. 9990003100
This is 1000 + 50 + 2050

I am looking to write a function (vb) that checks these 2 figures are equal as I want to run this repeatedly on multiple files on a monthly basis

In the first instance, how would I create a function to sum up miles from the middle rows

Thanks

Damian

 

[xlhelp]

Someone else may come with a better idea. You could do this with vba.

Public Sub addrows()

Dim R As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

N = 0
If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = 2 To Rng.Rows.Count - 1
num = Right(Cells(R, 1), 6) * 1

N = N + num

Next R

Cells(Rng.Rows.Count + 1, 1).Value = N

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Majority of the code from Chip Pearson

Member- AAAA Association Against Acronym Abusers

 

[CBasicAsslember]

This is a bit different. I like to pull the cell values into an array as it is faster then looping through cells.

Sub AddMileage()
Dim vData As Variant
Dim x As Long
Dim lTotal As Long

vData = Sheet1.Range("A1:A5")

For x = 1 To 5
    If Left(vData(x, 1), 1) = 1 Then
        lTotal = lTotal + Right(vData(x, 1), 6)
    
    Else
        If Left(vData(x, 1), 3) = 999 Then

        If lTotal <> Right(vData(x, 1), 6) Then MsgBox "Values don't match" & vbCr & Right(vData(x, 1), 6) _
        & " is not equal to " & lTotal
    
        End If
    End If
Next x

End Sub

 

[collierd]

I had to change Sheet1 to Active Sheet but that worked perfectly

Thanks