Solved: VBA - VALUE Error

[PJC]

I have the following code which works fine unless the second row of the workbook is used and a value is then input to A3. I get a VALUE error in G2. The first row are text boxes and I'm assuming this is causing problems?


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

[Bob Phillips]

Didn't reproduce the problem, but see if this overcomes it



Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

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
If PR = 1 Then PR = 2

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

ws_exit:
Application.EnableEvents = True
On Error GoTo 0
End Sub

[PJC]

Hi XLD,

This change sorted the problem on the first row, however, the calulcations are now not working on following rows. I replaced exiting code with yours, is that correct or should original just have been amended?

Thanks for the help,

Peter

[PJC]

I'm still having problems trying to get this coding right:banghead:

The first set of coding works fine, unless A2 and A3 have a value entered, and I get a VALUE error returned.

The second set posted solves the error if values are input to A2 and A3, but the the rest of sheet does not work! :doh:

Appreciate any direction on this one.

Peter

[OBP]

Peter, as your code works up to a point and XLD's prevents your value error by adding on error goto 0, but messes up the rest suggests that an error may be occurring that your resume doesn't see and his stops the VB, try this version that will trap the error and tell you what it is.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorMessage
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 & ")"

Exit_Worksheet_Change:
Exit Sub

ErrorMessage:
MsgBox Err.Description
Resume Exit_Worksheet_Change
End Sub

[OBP]

Alternatively can you post the worksheet with the code on so that we can see the error for ourselves.

[PJC]

Hi OBP,

Attached is a sample of the workbook. As you can see as soon as a value is entered in A the code calculates the previous reference, in this case sums F and subtracts B. So far so good.

If I was to put Ref 2 directly after Ref 1 it would create a VALUE error.

Here is the code.

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

Hope this makes sense!

Thanks, Peter.

[OBP]

You have a type mismatch error, because your formula in G2 is trying to sum from F1:F2

[PJC]

Apologies, it's been a long day!

The values in F are D*E.

The code is triggered by a value entered in A and sums the previous values in F and subtracts from earlier value in B.

Total of F - B is then placed in G.

There could be one line between values entered in A or many lines.

Sorry it's a bit long winded.

Thanks, Peter.

[OBP]

Peter, you misunderstood, that is what is causing your error in g2. You are trying to calculate a formula that includes the headings.

[PJC]

Ahhhhh.....yes I understand now.

How do I edit the code to exclude the text in row 1 at the start of the process. It's only an issue at the start as the code works perfectly well as soon as the second row is selected.

Thanks, Peter.

[OBP]

Sorry, I can't work it out LOL, I think you must need an if then with a different formula.
I don't think you just modify the current one.
Got to go now.

[PJC]

OPB

This thread has turned into a loop as your last reply confirms my original post:banghead:

I'm out of my depth now on how to get the firs text rows ignored in the coding.

If anyone has any thoughts they would be gratefully received.

Peter

[OBP]

Peter, I have removed the question that was here as I answered it for myself.
The attached worksheet has some VBA code that is no where as sophisticated as your code, it is old fashioned but it works.
Having solved your problem can I ask why you are using Excel rather Access?

[PJC]

Thanks OBP,

I'll have a look at the simpler version later :thumb

Why Excel over Access? I suppose when I started to put something together it was fairly simple, then the functionality grew as did the scope and before you know it Access may have been a better bet! I'll learn!

Thanks for you help and patience.

Peter