help with inputting code

[Morgan]

hi there,
i have some code (below) that takes the value of cell U17 every time it
changes and then lists it in a column on the 'graphs' sheet for charting
purposes. i would like to duplicate this code for another cell W25 having it
form a list on the 'graphs' sheet in column K, when i copied the code below
then pasted it into VB and amended the values it came up with a compile error

it must be something simple that i'm doing wrong, any ideas?


Private Sub Worksheet_Calculate()
Dim Dest As Range
With Sheets("graphs")
Set Dest = .Range("A" & Rows.Count).End(xlUp)
If Range("U17") <> Dest Then _
Dest.Offset(1) = Range("U17")
End With
End Sub

 

[JLatham]

Each worksheet can only have one event handler for each event. In your case,
you want to test for changes in 2 different cells, U17 and W25, so you have
to handle both in a single routine. And I don't think the Calculate() event
is a good one for this anyhow.

Make a copy of your workbook to test with and try the following in it.

First, delete the existing code you have. Replace it with this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range
Dim destColumn As String

If Target.Address <> "$U$17" And _
Target.Address <> "$W$25" Then
Exit Sub ' neither cell changed
End If
Select Case Target.Address
Case Is = "$U$17"
destColumn = "A"
Case Is = "$W$25"
destColumn = "K"
End Select
With Sheets("Graphs")
Set Dest = .Range(destColumn & Rows.Count).End(xlUp)
If Target.Value <> Dest.Value Then
Dest.Offset(0, 1) = Target
End If
End With
End Sub

That code should be placed into the code module for the sheet with U17 and
W25 on it. To check to see if it is in the right place when you're finished,
close the VB Editor and right-click on that sheet's name tab and choose [View
Code] and you should see it there.

 

[Morgan]

thanks for your efforts, it seems to kind of work, but as the values for the
two cells change i needed them to be inputted separately into their
respective columns on the 'graphs' sheet, thereby forming a list down the
page of each values that appears in those cells. Currently only the current
value of those two cells is being displayed on the graphs sheet,

any ideas?

Each worksheet can only have one event handler for each event. In your case,
you want to test for changes in 2 different cells, U17 and W25, so you have
to handle both in a single routine. And I don't think the Calculate() event
is a good one for this anyhow.

Make a copy of your workbook to test with and try the following in it.

First, delete the existing code you have. Replace it with this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range
Dim destColumn As String

If Target.Address <> "$U$17" And _
Target.Address <> "$W$25" Then
Exit Sub ' neither cell changed
End If
Select Case Target.Address
Case Is = "$U$17"
destColumn = "A"
Case Is = "$W$25"
destColumn = "K"
End Select
With Sheets("Graphs")
Set Dest = .Range(destColumn & Rows.Count).End(xlUp)
If Target.Value <> Dest.Value Then
Dest.Offset(0, 1) = Target
End If
End With
End Sub

That code should be placed into the code module for the sheet with U17 and
W25 on it. To check to see if it is in the right place when you're finished,
close the VB Editor and right-click on that sheet's name tab and choose [View
Code] and you should see it there.

hi there,
i have some code (below) that takes the value of cell U17 every time it
changes and then lists it in a column on the 'graphs' sheet for charting
purposes. i would like to duplicate this code for another cell W25 having it
form a list on the 'graphs' sheet in column K, when i copied the code below
then pasted it into VB and amended the values it came up with a compile error

it must be something simple that i'm doing wrong, any ideas?


Private Sub Worksheet_Calculate()
Dim Dest As Range
With Sheets("graphs")
Set Dest = .Range("A" & Rows.Count).End(xlUp)
If Range("U17") <> Dest Then _
Dest.Offset(1) = Range("U17")
End With
End Sub

 

[JLatham]

It should work. I'll make the same offer that has been made elsewhere: send
a copy to me as an attachment to an email and I'll see what I can do with it.
Send the email to (remove spaces) HelpFrom @ JLatham Site. com

thanks for your efforts, it seems to kind of work, but as the values for the
two cells change i needed them to be inputted separately into their
respective columns on the 'graphs' sheet, thereby forming a list down the
page of each values that appears in those cells. Currently only the current
value of those two cells is being displayed on the graphs sheet,

any ideas?

Each worksheet can only have one event handler for each event. In your case,
you want to test for changes in 2 different cells, U17 and W25, so you have
to handle both in a single routine. And I don't think the Calculate() event
is a good one for this anyhow.

Make a copy of your workbook to test with and try the following in it.

First, delete the existing code you have. Replace it with this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range
Dim destColumn As String

If Target.Address <> "$U$17" And _
Target.Address <> "$W$25" Then
Exit Sub ' neither cell changed
End If
Select Case Target.Address
Case Is = "$U$17"
destColumn = "A"
Case Is = "$W$25"
destColumn = "K"
End Select
With Sheets("Graphs")
Set Dest = .Range(destColumn & Rows.Count).End(xlUp)
If Target.Value <> Dest.Value Then
Dest.Offset(0, 1) = Target
End If
End With
End Sub

That code should be placed into the code module for the sheet with U17 and
W25 on it. To check to see if it is in the right place when you're finished,
close the VB Editor and right-click on that sheet's name tab and choose [View
Code] and you should see it there.

hi there,
i have some code (below) that takes the value of cell U17 every time it
changes and then lists it in a column on the 'graphs' sheet for charting
purposes. i would like to duplicate this code for another cell W25 having it
form a list on the 'graphs' sheet in column K, when i copied the code below
then pasted it into VB and amended the values it came up with a compile error

it must be something simple that i'm doing wrong, any ideas?


Private Sub Worksheet_Calculate()
Dim Dest As Range
With Sheets("graphs")
Set Dest = .Range("A" & Rows.Count).End(xlUp)
If Range("U17") <> Dest Then _
Dest.Offset(1) = Range("U17")
End With
End Sub