Can't Seem to find problem with this VBA Code
This portion:
Target.Offset(0, -2).Value
Refers to the cell that is 2 columns to the left. If you're typing something
into column A (or even column B), then there's trouble.
I'm not quite sure if this fits your needs, but I'd do the checking up front:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Cells.Count 1 Then Exit Sub
If IsNumeric(.Value) = False Then Exit Sub
If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If
If Target.Value <= 1 Then Exit Sub
On Error GoTo errHandler:
Application.EnableEvents = False
Select Case .Column
Case Is = 6
.Offset(0, 3).Value = .Value - .Offset(0, 2).Value
Case Is = 8 'only thing left
.Offset(0, 1).Value = .Offset(0, -2).Value - .Value
End Select
End With
errHandler:
Application.EnableEvents = True
End Sub
The .enableevents stuff tells excel to not invoke the worksheet_change event
when the code writes to the worksheet.
Kevin Baker wrote:
If Target.Column = 6 And Target.Value 1 And Target.Offset(0, 2).Value 1
Then
Target.Offset(0, 3).Value = Target.Value - Target.Offset(0, 2).Value
ElseIf Target.Column = 8 And Target.Value 1 And
Target.Offset(0, -2).Value 1 Then
Target.Offset(0, 1).Value = Target.Offset(0, -2).Value - Target.Value
End If
I get the following when entering data in Cell A on a new line:
"Run-Time error '1004'
Application-defined or object-defined error
Any thoughts?
Kevin
--
Dave Peterson
|