| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value issue
Someone in the Charting forum provided me the code below (which I have
modified) in my efforts to create a dynamic chart. It is intended place the value of the cells in column C into column B. My problem is, for my dynamic chart to work correctly, if there is a value in cell (say C3) then I need that value placed in Cell B3. If the formula in cell C3 has returned no value I need Cell B3 to be left blank, but the code below places a 0 in any cell in column B were the formula in the corresponding cell in column C returns no value. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "E3:E1000" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If ws_exit: Application.EnableEvents = True End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Value issue
Patrick try giving this a go
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value ELSE Me.Range("B3:B1000").Value = "" End If "Patrick C. Simonds" wrote: Someone in the Charting forum provided me the code below (which I have modified) in my efforts to create a dynamic chart. It is intended place the value of the cells in column C into column B. My problem is, for my dynamic chart to work correctly, if there is a value in cell (say C3) then I need that value placed in Cell B3. If the formula in cell C3 has returned no value I need Cell B3 to be left blank, but the code below places a 0 in any cell in column B were the formula in the corresponding cell in column C returns no value. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "E3:E1000" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If ws_exit: Application.EnableEvents = True End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Value issue
If column C values are derived from embedded formulas then :
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then This statement will always be true because it will see the formula but will read the value of the cell as zero if no greater value has been calculated by the embedded formula. To avoid that, change the criteria like: If Intersect(Target, Me.Range(WS_RANGE)).Value 0 Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If "Patrick C. Simonds" wrote: Someone in the Charting forum provided me the code below (which I have modified) in my efforts to create a dynamic chart. It is intended place the value of the cells in column C into column B. My problem is, for my dynamic chart to work correctly, if there is a value in cell (say C3) then I need that value placed in Cell B3. If the formula in cell C3 has returned no value I need Cell B3 to be left blank, but the code below places a 0 in any cell in column B were the formula in the corresponding cell in column C returns no value. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "E3:E1000" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If ws_exit: Application.EnableEvents = True End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Value issue
I have tried this code and the code of the other responder, to no avail. I
still get a 0 value in the B column where the corresponding cell in the E column is blank. Just to go over a couple of points: Column E cells are empty until the user inputs a number Column C cells contains a formula which is calculated after a number is placed in Column E Column B cells are empty and must remain empty unless there is a number entered into the corresponding cell in the E column What is happening now is that the value of all cells in the C column are being placed into the corresponding cells in the B column whether there is a value in the corresponding cell in the E column or not. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "E3:E1000" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Intersect(Target, Me.Range(WS_RANGE)).Value 0 Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If ws_exit: Application.EnableEvents = True End Sub "JLGWhiz" wrote in message ... If column C values are derived from embedded formulas then : If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then This statement will always be true because it will see the formula but will read the value of the cell as zero if no greater value has been calculated by the embedded formula. To avoid that, change the criteria like: If Intersect(Target, Me.Range(WS_RANGE)).Value 0 Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If "Patrick C. Simonds" wrote: Someone in the Charting forum provided me the code below (which I have modified) in my efforts to create a dynamic chart. It is intended place the value of the cells in column C into column B. My problem is, for my dynamic chart to work correctly, if there is a value in cell (say C3) then I need that value placed in Cell B3. If the formula in cell C3 has returned no value I need Cell B3 to be left blank, but the code below places a 0 in any cell in column B were the formula in the corresponding cell in column C returns no value. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "E3:E1000" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If ws_exit: Application.EnableEvents = True End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Value issue
Patrick, check to see if you have Display Zero Values checked in the
ToolsOptionsView. If you do, uncheck it and see if you still get the zeroes. "Patrick C. Simonds" wrote: I have tried this code and the code of the other responder, to no avail. I still get a 0 value in the B column where the corresponding cell in the E column is blank. Just to go over a couple of points: Column E cells are empty until the user inputs a number Column C cells contains a formula which is calculated after a number is placed in Column E Column B cells are empty and must remain empty unless there is a number entered into the corresponding cell in the E column What is happening now is that the value of all cells in the C column are being placed into the corresponding cells in the B column whether there is a value in the corresponding cell in the E column or not. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "E3:E1000" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Intersect(Target, Me.Range(WS_RANGE)).Value 0 Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If ws_exit: Application.EnableEvents = True End Sub "JLGWhiz" wrote in message ... If column C values are derived from embedded formulas then : If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then This statement will always be true because it will see the formula but will read the value of the cell as zero if no greater value has been calculated by the embedded formula. To avoid that, change the criteria like: If Intersect(Target, Me.Range(WS_RANGE)).Value 0 Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If "Patrick C. Simonds" wrote: Someone in the Charting forum provided me the code below (which I have modified) in my efforts to create a dynamic chart. It is intended place the value of the cells in column C into column B. My problem is, for my dynamic chart to work correctly, if there is a value in cell (say C3) then I need that value placed in Cell B3. If the formula in cell C3 has returned no value I need Cell B3 to be left blank, but the code below places a 0 in any cell in column B were the formula in the corresponding cell in column C returns no value. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "E3:E1000" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If ws_exit: Application.EnableEvents = True End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Value issue
No it is not checked. As a matter of fact zeros are not visible in the
cells. But if you select the cell there is a zero in the formula area. "JLGWhiz" wrote in message ... Patrick, check to see if you have Display Zero Values checked in the ToolsOptionsView. If you do, uncheck it and see if you still get the zeroes. "Patrick C. Simonds" wrote: I have tried this code and the code of the other responder, to no avail. I still get a 0 value in the B column where the corresponding cell in the E column is blank. Just to go over a couple of points: Column E cells are empty until the user inputs a number Column C cells contains a formula which is calculated after a number is placed in Column E Column B cells are empty and must remain empty unless there is a number entered into the corresponding cell in the E column What is happening now is that the value of all cells in the C column are being placed into the corresponding cells in the B column whether there is a value in the corresponding cell in the E column or not. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "E3:E1000" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Intersect(Target, Me.Range(WS_RANGE)).Value 0 Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If ws_exit: Application.EnableEvents = True End Sub "JLGWhiz" wrote in message ... If column C values are derived from embedded formulas then : If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then This statement will always be true because it will see the formula but will read the value of the cell as zero if no greater value has been calculated by the embedded formula. To avoid that, change the criteria like: If Intersect(Target, Me.Range(WS_RANGE)).Value 0 Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If "Patrick C. Simonds" wrote: Someone in the Charting forum provided me the code below (which I have modified) in my efforts to create a dynamic chart. It is intended place the value of the cells in column C into column B. My problem is, for my dynamic chart to work correctly, if there is a value in cell (say C3) then I need that value placed in Cell B3. If the formula in cell C3 has returned no value I need Cell B3 to be left blank, but the code below places a 0 in any cell in column B were the formula in the corresponding cell in column C returns no value. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "E3:E1000" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If ws_exit: Application.EnableEvents = True End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Value issue
My formula in column C was an IF statement that said if the condition did
not equal a positive value then the cell value would be null [""]. Don't know if yours is set up that way, but if not, that could be the problem. OK, I'm not getting the zeros, so I am out of suggestions. "Patrick C. Simonds" wrote: No it is not checked. As a matter of fact zeros are not visible in the cells. But if you select the cell there is a zero in the formula area. "JLGWhiz" wrote in message ... Patrick, check to see if you have Display Zero Values checked in the ToolsOptionsView. If you do, uncheck it and see if you still get the zeroes. "Patrick C. Simonds" wrote: I have tried this code and the code of the other responder, to no avail. I still get a 0 value in the B column where the corresponding cell in the E column is blank. Just to go over a couple of points: Column E cells are empty until the user inputs a number Column C cells contains a formula which is calculated after a number is placed in Column E Column B cells are empty and must remain empty unless there is a number entered into the corresponding cell in the E column What is happening now is that the value of all cells in the C column are being placed into the corresponding cells in the B column whether there is a value in the corresponding cell in the E column or not. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "E3:E1000" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Intersect(Target, Me.Range(WS_RANGE)).Value 0 Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If ws_exit: Application.EnableEvents = True End Sub "JLGWhiz" wrote in message ... If column C values are derived from embedded formulas then : If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then This statement will always be true because it will see the formula but will read the value of the cell as zero if no greater value has been calculated by the embedded formula. To avoid that, change the criteria like: If Intersect(Target, Me.Range(WS_RANGE)).Value 0 Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If "Patrick C. Simonds" wrote: Someone in the Charting forum provided me the code below (which I have modified) in my efforts to create a dynamic chart. It is intended place the value of the cells in column C into column B. My problem is, for my dynamic chart to work correctly, if there is a value in cell (say C3) then I need that value placed in Cell B3. If the formula in cell C3 has returned no value I need Cell B3 to be left blank, but the code below places a 0 in any cell in column B were the formula in the corresponding cell in column C returns no value. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "E3:E1000" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If ws_exit: Application.EnableEvents = True End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Value issue
Thank you so much, I can't tell you how much I appreciate all your help. You
of course were exactly right, my formula was the guilty party, it in fact was returning the zero value. "JLGWhiz" wrote in message ... My formula in column C was an IF statement that said if the condition did not equal a positive value then the cell value would be null [""]. Don't know if yours is set up that way, but if not, that could be the problem. OK, I'm not getting the zeros, so I am out of suggestions. "Patrick C. Simonds" wrote: No it is not checked. As a matter of fact zeros are not visible in the cells. But if you select the cell there is a zero in the formula area. "JLGWhiz" wrote in message ... Patrick, check to see if you have Display Zero Values checked in the ToolsOptionsView. If you do, uncheck it and see if you still get the zeroes. "Patrick C. Simonds" wrote: I have tried this code and the code of the other responder, to no avail. I still get a 0 value in the B column where the corresponding cell in the E column is blank. Just to go over a couple of points: Column E cells are empty until the user inputs a number Column C cells contains a formula which is calculated after a number is placed in Column E Column B cells are empty and must remain empty unless there is a number entered into the corresponding cell in the E column What is happening now is that the value of all cells in the C column are being placed into the corresponding cells in the B column whether there is a value in the corresponding cell in the E column or not. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "E3:E1000" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Intersect(Target, Me.Range(WS_RANGE)).Value 0 Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If ws_exit: Application.EnableEvents = True End Sub "JLGWhiz" wrote in message ... If column C values are derived from embedded formulas then : If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then This statement will always be true because it will see the formula but will read the value of the cell as zero if no greater value has been calculated by the embedded formula. To avoid that, change the criteria like: If Intersect(Target, Me.Range(WS_RANGE)).Value 0 Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If "Patrick C. Simonds" wrote: Someone in the Charting forum provided me the code below (which I have modified) in my efforts to create a dynamic chart. It is intended place the value of the cells in column C into column B. My problem is, for my dynamic chart to work correctly, if there is a value in cell (say C3) then I need that value placed in Cell B3. If the formula in cell C3 has returned no value I need Cell B3 to be left blank, but the code below places a 0 in any cell in column B were the formula in the corresponding cell in column C returns no value. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "E3:E1000" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Me.Range("B3:B1000").Value = Me.Range("C3:C1000").Value End If ws_exit: Application.EnableEvents = True End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sum issue | Excel Worksheet Functions | |||
| Help with known issue | Excel Programming | |||
| Different day, different IF issue | Excel Discussion (Misc queries) | |||
| Yet another issue | Excel Worksheet Functions | |||
| With and End With issue | Excel Programming | |||
Linear Mode
