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

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

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

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

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

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

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

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