View Full Version : Solved: worksheet change event
ndendrinos
03-15-2011, 03:06 PM
In range: C17:C35
If the value of A>B the C=[A-B]
example:
.................A.............B...............C
1...............7..............7
2...............3..............3
3...............5..............2................3
4...............4..............4
The result in C (if any) must be a numeral and not the result of a formula
Similar to this but good for all of range C17:C35
Private Sub Worksheet_Change(ByVal Target As Range)
[C17] = [A17-B17]
End Sub
Thank you
mdmackillop
03-15-2011, 03:35 PM
I don't understand what you are after, and can't make sense of your data.
mancubus
03-15-2011, 03:51 PM
assuming changes in cells in range A17:A35 will trigger the code, try (adopt) below:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
If Target.Row < 17 Or Target.Row > 35 Then Exit Sub
If Target.Value <= Target.Offset(, 1).Value Then Exit Sub
Target.Offset(, 2).Value = Target.Value - Target.Offset(, 1).Value
End Sub
ndendrinos
03-15-2011, 04:06 PM
thank you but not there yet.
Maybe if I explain it like this
in A17 I type numral 5
in B17 I type numeral 5
in C17( the sum of A17 less B17) I get nothing since A17-B17=zero
Now I change b17 from 5 to 2
C17 should now equal 3
The 3 has to be a numeral and not a formula .
This should work for as many times as I change a cell in range B17:B35
ndendrinos
03-15-2011, 04:39 PM
maybe like this is easier?
If Not Intersect(Target, Range("B17:B35")) Is Nothing Then
[c] = [a-b]
End If
Of course I get an error if I try like this
and the error is run time error '424'
Object required
ndendrinos
03-15-2011, 04:42 PM
same error whith this
If Not Intersect(Target, Range("B17:B35")) Is Nothing Then
[c].Value = [a].Value - [b].Value
End If
mdmackillop
03-15-2011, 04:59 PM
If Not Intersect(Target, Range("B17:B35")) Is Nothing Then
x = target.row
cells(x,3) = cells(x,1)-cells(x,2)
End If
ndendrinos
03-15-2011, 05:10 PM
Exactly and thank you very much mdmackillop
ndendrinos
03-16-2011, 09:55 AM
The code offered here works but created a small problem for me in that in the empty cells and only at the first row there was a zero instead of beeing "empty". Anyone with the same problem can find the answer to this here:
http://www.mrexcel.com/forum/showthread.php?p=2647936#post2647936
(http://www.mrexcel.com/forum/showthread.php?p=2647936#post2647936)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.