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

Range("celladdress").Value = Target

or if it should be on a different sheet:

Sheets("nameofthesheet").Range("celladdress").Valu e = Target

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

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"New Ton" wrote:

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

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

"Wigi" wrote:

Range("celladdress").Value = Target

or if it should be on a different sheet:

Sheets("nameofthesheet").Range("celladdress").Valu e = Target

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

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"New Ton" wrote:

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

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

"Wigi" wrote:

Range("celladdress").Value = Target

or if it should be on a different sheet:

Sheets("nameofthesheet").Range("celladdress").Valu e = Target

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

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"New Ton" wrote:

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

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

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

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.


"Lars-Ã…ke Aspelin" wrote:

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

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

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

On Fri, 8 Aug 2008 10:10:00 -0700, New Ton
wrote:

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.


"Lars-Åke Aspelin" wrote:

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

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

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

I got this from Dave Peterson;

Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Me.Range("g31").Value = Me.Range("g6").Value
Application.EnableEvents = True
End Sub

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

"Gord Dibben" wrote:

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

On Fri, 8 Aug 2008 10:10:00 -0700, New Ton
wrote:

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.


"Lars-Ã…ke Aspelin" wrote:

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

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

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.

New Ton wrote:

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

I got this from Dave Peterson;

Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Me.Range("g31").Value = Me.Range("g6").Value
Application.EnableEvents = True
End Sub

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

"Gord Dibben" wrote:

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

On Fri, 8 Aug 2008 10:10:00 -0700, New Ton
wrote:

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.


"Lars-Ã…ke Aspelin" wrote:

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

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




--

Dave Peterson