Help with vba Code

[mmkoll]

hey there this is my first post so

My code is working but it got a problem and i dont know how to fix it.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
If Not Application.Intersect(Target, Me.Range("D8:K29")) Is Nothing Then
r = Target.Row
c = Target.Column
While Not IsEmpty(Cells(r, c).Value)
Cells(r, c).Value = Cells(6, c).Value - Cells(r, c).Value
c = c + 1
Wend
Application.EnableEvents = True
End If
End Sub

what the code thoes it subtract a valor that you tipe from other that is in the table.

A	b	c	d	e	f
1	14	15	12	12
2					=sum(b2:e2)
3
4
5

ok this is example of my table.
every time you insert a number in the range(b2:e5) it subtract that number from the respective range(a1:e1)
ex: if you insert a valor in b2 valor "3" the valor of b2 will be "11"

all good till there, the problem is the last column range(e2:e5) wend i insert a valor, dosent show the right result and it erases the formula in f2.

I attach my real table in the post that reflects my code.

can anywone help?

Tanks in advance

[westconn1]

try replacing

While Not IsEmpty(Cells(r, c).Value)
Cells(r, c).Value = Cells(6, c).Value - Cells(r, c).Value
c = c + 1
Wend

with

for c = target.column to 5
  if isempty(cells(r, c) then exit for
  Cells(r, c).Value = Cells(r - 1, c).Value - Cells(r, c).Value
next

the code you posted here should work, but the code in your workbook is not correct
remove on error resume next and fix any errors that may occur, instead of hiding them

[mmkoll]

Tanks for the reply,

"on error resume next" i wosent seeing that tanks for tip.

ok, after debuging my table it gives me "error 13" Type Mysmatch wend it reatches the last column on this piece of code.Any Idea

Cells(r, c).Value = Cells(6, c).Value - Cells(r, c).Value

your code, IF dosent accept "isempty" so i tryed this but the substract dosent work

For c = Target.Column To 11
    If (Cells(r, c)) = "" Then Exit For
    Cells(r, c).Value = Cells(r - 1, c).Value - Cells(r, c).Value
Next c

tanks

[p45cal]

Try this in the Folha1 code-module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ChangedCells As Range, cll As Range
On Error GoTo Here
Set ChangedCells = Application.Intersect(Target, Me.Range("D8:K29"))
If Not ChangedCells Is Nothing Then
  Application.EnableEvents = False
  For Each cll In ChangedCells.Cells
    cll.Value = Cells(6, cll.Column).Value - cll.Value
  Next cll
End If
Here:
Application.EnableEvents = True
End Sub

[mmkoll]

ok it seems i make it to work using your code, but like this

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Me.Range("D8:K29")) Is Nothing Then
    r = Target.Row
    c = Target.Column
    For c = Target.Column To 11
    If (Cells(r, c)) = 0 Then Exit For
    Cells(r, c).Value = Cells(6, c).Value - Cells(r, c).Value
    c = c + 1
Next c
Application.EnableEvents = True
End If
End Sub

Ofter some tests its working fine treath Solved

[mmkoll]

Hello p45call tanks for your code,

it also works fine and you give some ideas for the other that i have. many tanks