Specify target cell

N

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 :)
 
W

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.
 
N

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:)
 
N

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:)
 
L

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 :)
Click to expand...


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
 
N

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.
 
G

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
 
N

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:)
 
D

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.
 
Top