VB code to total up a column as the value change on a nother

[Fernando]

Hello Everyone,

I got a Excel tabel like below.
I want to write a code to Total up some figures.
Every Change in Date (Column A), Flight No (Column B), Out/In(Column
D), Column H's figures should total up to "Column I" as it shown in
the example above.
Like wise Column J's figures should total up to Column K.
As user add records i want it to automaticaly update Column I and
Column K.
I want to keep Colum I and Column K locked.



A B D H I J K
Date Flight Out/ Total Total Return Return
No. In Per Per Per Per
Series Flight Series Flight

10.01.05 878 Out 6 2
10.01.05 878 Out 10 16 3 5
10.01.05 878 In 15 5
10.01.05 878 In 10 4
10.01.05 878 In 10 35 9 18
10.01.05 578 Out 15 5
10.01.05 578 Out 12 27 3 8
10.01.05 578 In 20 6
10.01.05 578 In 20 40 4 10
11.01.05 878 Out 6 2
11.01.05 878 Out 10 16 3 5
11.01.05 878 In 10 4
11.01.05 878 In 10 20 9 13
11.01.05 578 Out 15 5
11.01.05 578 Out 12 27 3 8
11.01.05 578 In 20 6
11.01.05 578 In 20 40 4 10




Is there a way to do this in VB coding?

A help will be really appriciated.

Thank you very much in advance.

 

[Tom Ogilvy]

Where does the first line of data start? What Row?

10.01.05 878 Out 6 2

What row would that be in?

 

[Priyanga Fernando]

Hello Mr Ogivy,

Sorry earlier I cut and past it from words, now i type the whole table
again I guess now its readable.

I'll repeat the q.

I want to write a code to Total up some figures.
Every Change in Date (Column A), Flight No (Column B), Out/In(Column D),
Column H’s figures should total up to "Column I" as it shown in the
example above.
Like wise Column J’s figures should total up to Column K.
As user add records i want it to automaticaly update Column I and Column
K.


A B D H I J K
Date Flight Out/ Total Total Return Return
No In per per per per
series flight series flight

10.01.05 878 out 6 2
10.01.05 878 out 10 16 3 5
10.01.05 878 in 15 5
10.01.05 878 in 10 4
10.01.05 878 in 10 35 9 18
10.01.05 578 out 15 5
10.01.05 578 out 12 27 3 8
10.01.05 578 in 20 6
10.01.05 578 in 20 40 4 10
11.01.05 878 out 6 2
11.01.05 878 out 10 16 3 5
11.01.05 878 in 10 4
11.01.05 878 in 10 20 9 13
11.01.05 578 out 15 5
11.01.05 578 out 12 27 3 8
11.01.05 578 in 20 6
11.01.05 578 in 20 40 4 10

Remember it's the same sheet that you write this code.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range, cell As Range, bFound As Boolean
ActiveSheet.Unprotect
If Target.Count > 1 Then Exit Sub
If Target.Address = "$G$2" Then
Cells.Interior.ColorIndex = xlNone
'To select the whole column of the "Seals From"
Set rng = Range(Cells(5, 6), Cells(Rows.Count, 6).End(xlUp))
bFound = False
For Each cell In rng
If cell.Value <= Target And cell.Offset(0, 1).Value >= Target.Value
Then
If Not bFound Then
Cells(cell.Row, "J").Select
bFound = True
End If
cell.EntireRow.Interior.ColorIndex = 6
End If
Next
End If
ActiveSheet.Protect

End Sub