Specify target cell

[New Ton]

Hello. I am trying to get an output to a specific cell after change in any
cell in a range.

This code does what i want, except it offsets target next to cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("h5:h30"), Target) Is Nothing Then
Target.Offset(0, 1).Value = Target
End If
End Sub

How can I replace Target.Offset with only one specific cell for all change
in range?

Thanks in advance

 

[Wigi]

Range("celladdress").Value = Target

or if it should be on a different sheet:

Sheets("nameofthesheet").Range("celladdress").Value = Target

If the output cell is part of the range you check in your code, turn off
Events temporarily using Application.EnableEvents.

 

[New Ton]

Thank you very much.
I thought i had tried that, but maybe i skipped a space in the code or
something.
Anyways, that did the trick

 

[New Ton]

Ok, so far so good. But it seems like it only outputs once.
If another cell in range (h5:h30) changes value after, nothing outputs to
(h31)
Can someone help?
Thanks in advance

 

[Lars-Åke Aspelin]

On Fri, 8 Aug 2008 00:16:01 -0700, New Ton <New

Hello. I am trying to get an output to a specific cell after change in any
cell in a range.

This code does what i want, except it offsets target next to cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("h5:h30"), Target) Is Nothing Then
Target.Offset(0, 1).Value = Target
End If
End Sub

How can I replace Target.Offset with only one specific cell for all change
in range?

Thanks in advance

Maybe like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("h5:h30"), Target) Is Nothing Then
Range("I10").Value = Target.Value
End If
End Sub

Hope this helps / Lars-Åke

 

[New Ton]

Thank you all very much for helping out, it all worked

But after I place formula (using row 6 as example) =IF(C6+E6=0;"";B6-C6+E6)
in range "h5:h30" for adding content, Excel will not add that same content in
"H31". That is what this thread was all about in the first place.

Does it have anything to do with manual and application input?
Application.Enablevents?

When i change 'If Not Application.Intersect' to 'If Application.Intersect'
in the code, it seems to add whatever is added in C6 or E6.

 

[Gord Dibben]

It has to with the type of event.

Change event won't react to a calculated value change.

Private Sub Worksheet_Calculate() is the event type you need.


Gord Dibben MS Excel MVP

 

[New Ton]

Unfornunatley I have two threads going with the same problem, sorry about that.

I got this from Dave Peterson;

and it works, except it only returns for "g6"
how can i get it to continue returning value for "g7", "g8", "g9" etc?

Thanks for your great help so far

 

[Dave Peterson]

It's a PITA with multiple threads for the same question.

I'm not sure what you want, but I replied at the other thread.