Solved: VBA Help!

[PJC]

I have a simple workbook (see attached) with code as below.

[VBA]
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column <> 1 Or Target.Row = 1 Or Target.Row = 2 _
Or Target.Value = "" Then Exit Sub

If Err.Number = 2015 Then Exit Sub

Dim PR As Integer

PR = ActiveSheet.Cells(Target.Row, 1).End(xlUp).Row

ActiveSheet.Cells(Target.Row - 1, 7).Formula = _
"=B" & PR & "-Sum(F" & PR & ":F" & Target.Row - 1 & ")"
End Sub
[/VBA]

The macro does the following in G. When the user inputs a value in A the macro sums the previous values in F and subtracts from the previous value in B.

In this example G3 would be B2 minus sum of F2+F3.
G4 would be B4 minus F4.
G5 would be B5 minus F5.

As you can see in the attached all works well until there are consecutive single value entries in F.

Any ideas how to fix this problem? Thanks in advance.

[malik641]

You know I was worried that might happen

[vba]Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column <> 1 Or Target.Row = 1 Or Target.Row = 2 _
Or Target.Value = "" Then Exit Sub

If Err.Number = 2015 Then Debug.Print Err.Description: Exit Sub

Dim PR As Integer

PR = ActiveSheet.Cells(Target.Row, 1).End(xlUp).Row

If ActiveSheet.Cells(Target.Row - 1, 1) <> "" Then
ActiveSheet.Cells(Target.Row, 7).Formula = _
"=B" & PR & "-Sum(F" & PR & ":F" & Target.Row - 1 & ")"
Else
ActiveSheet.Cells(Target.Row - 1, 7).Formula = _
"=B" & PR & "-Sum(F" & PR & ":F" & Target.Row - 1 & ")"
End If
End Sub[/vba]

[mdmackillop]

Some Questions:
How is column 1 filled? Always below the previous entry, or do you change/delete existing values.
Will there be blanks in Col 1? If so PR will be the row below the blank (assuming data entered below) or it will be 1 if there are no blanks.
Can you type in Col H the formulae you expect to see in Col G, and why.
The function and explanation are not clear, and all seems to hinge around blank values in Col 1 and what you want to happen.
You say "sums the previous values in F". Is this always from F2 or some other starting point?
Regards
MD

[PJC]

Still struggling to get this to work.

MD,

In answer to your questions ----

Column A is filled by a user manually inputting a reference number, not always on the next line as there may have been several codes selected (column C) on the previous reference number.
As per my example there was a gap between Ref 122 and 124. Sometimes there may not be a gap as you can see between Ref's 124/126/129 on my example. Once the reference numbers have been input there will be no changes or deletions. So, yes, at times there will be blanks in column 1.

See attached for the formulas in G.

The trigger is the user entering a value in A which triggers the formula in G. The difficulty has been adjusting for the fact there may be several values in F for each value in A or just one depending on how many codes are selected in column C.

Does this make it clearer? Really appreciate your help as I'm struggling!

Peter.

[mdmackillop]

Hi Peter
Please enter a few more rows of data. There's just not enough there to see a sequence. If the problem was clearly set out, I'm sure it would be solved by now, but I've spent all my time trying to understand it.
Regards
MD

[PJC]

MD,

Attached is a workbook with more entries. As you can see the macro works fine unless you get two consecutive rows with one total amt and one code selected (column C) and there is an error in the second row.

I've marked the rows where the error is.

Thanks for your patience.

Peter.

[malik641]

Hey Pete

My attached code didn't work for you? It seemed to work for me

[PJC]

Hi Malik,

I ran your new code and the results are attached.

As you can see it worked for ref 126.

It calculated ref 127 correctly but the value was placed in G5 rather than G4.

For reference 128 it calculated the value as 500 rather than 250.

Am I missing something here? Thanks again, Pete.

[PJC]

Now resolved. Thanks MD and Malik for your help.

[Norie]

Was it perhaps solved here?

[PJC]

Hi Norrie,

Actually no. A colleagues brother-in-law came up with this alternative, which does the job.

[VBA] Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

Dim theTargetRow As Integer
Dim bSameLine As Boolean
Dim i As Integer
Dim PR As Integer

If Target.Column = 5 Then

PR = ActiveSheet.Cells(Target.Row, 1).End(xlUp).Row

If ActiveSheet.Cells(Target.Row - 1, 1).Value <> "" Then
theTargetRow = Target.Row
bSameLine = True
Else
theTargetRow = Target.Row
bSameLine = False
End If

If ActiveSheet.Cells(Target.Row, 1).Value = "" Then

For i = PR To Target.Row
ActiveSheet.Cells(i, 7).Formula = ""
Next

ActiveSheet.Cells(theTargetRow, 7).Formula = _
"=B" & PR & "-Sum(F" & PR & ":F" & theTargetRow & ")"

Else
ActiveSheet.Cells(theTargetRow, 7).Formula = _
"=B" & theTargetRow & "-Sum(F" & theTargetRow & ":F" & theTargetRow & ")"

End If

End If

End Sub[/VBA]