Help with vba Code

[mmkoll]

hey there this is my first post so :hi:

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:dunno


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