Macro Output Question

  • Thread starter Thread starter JR
  • Start date Start date
J

JR

Hello,

I was given some excellent help on the forum, and thanks to all. Need help
with one more thing to finish. I was given the below macro to be able to
enter a number in one cell and the output cell would continuously add up the
sum. I need to take the output sum and subtract it from another cell, but I
keep getting an error. What do I need to do to make this happen.



Thanks JR


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("C2:C15")) Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = .Offset(0, 1).Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub
 
Hi JR

see notes in-line in the code
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("C2:C15")) Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = .Offset(0, 1).Value + .Value
Click to expand...
'the line of code above increments the value of the cell
'so to subtract this value from another cell - change A1 to the relevant
cell
Range("A1").Value = Range("A1").Value -
..Offset(0,1).Value
Application.EnableEvents = True
End If
End If
End With
End Sub
Click to expand...


Cheers
JulieD
 
What kind of error do you get?
Can't you just use a formula to do this subtraction?

Regards,

Peo Sjoblom
 
Hello,

I am sorry, I am not explaining myself very well. I am using the below macro
do determine a value in a cell, and I need to take this value as a digit if
possible and subtract it from another cell.

Example:

Input to cell A1 = 1, and out put is B1=1, now I need to take this
calculated value in B1 and subtract it from the value in D1. I think my
problem is that it is not letting me take just the output value in B1.

Thanks JR


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("A1:A10")) Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = .Offset(0, 1).Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub
 
if you have
=D1-B1

in another cell it should work ... this number should change after the code
runs - is this what you're trying to do

Cheers
JulieD
 
Hello,

I get this error when I enter any number in any other cell:

runtime error 91
object variable or with block variable not set

refers to this line in the macro

If Not Intersect(.Cells, Range("A1:A10")) Then

Thanks JR

 
Back
Top