If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
help with inputting code
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 -- thanks |
#2
|
|||
|
|||
help with inputting code
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" wrote: 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 -- thanks |
#3
|
|||
|
|||
help with inputting code
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? "JLatham" wrote: 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" wrote: 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 -- thanks |
#4
|
|||
|
|||
help with inputting code
It should work. I'll make the same offer that has been made elsewhe 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 "Morgan" wrote: 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? "JLatham" wrote: 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" wrote: 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 -- thanks |
Thread Tools | |
Display Modes | |
|
|