Value issue

[Patrick C. Simonds]

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

 

[JRForm]

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

 

[JLGWhiz]

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]

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]

Patrick, check to see if you have Display Zero Values checked in the
Tools>Options>View. If you do, uncheck it and see if you still get the
zeroes.

 

[Patrick C. Simonds]

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]

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]

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.

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.

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.