This Formula Returns Blanks As Zeros, Can We Fix

[rockyw]

This formula works fine but it returns blanks as 0s, that messes up some condisional formating. I read where "" in the formula will stop the 0s from being returned? If so where would I insert them? Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("P:P,B:B")) Is Nothing Then
        With ThisWorkbook
            .Sheets("Sheet2").Range("E3:E24").Value = .Sheets("Sheet3").Range("E3:E24").Value
        End With
    End If
End Sub

 

[rockyw]

OR! can this formula be told to skip black cells and not return anything? Thanks

 

[repairman615]

Hello,

Try this:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Application.Intersect(Target, Range("P:P,B:B")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> ThisWorkbook<br>            <SPAN style="color:#00007F">If</SPAN> .Sheets("Sheet2").Range("E3:E24").Value <> 0 Then _<br>            .Sheets("Sheet2").Range("E3:E24").Value = _<br>            .Sheets("Sheet3").Range("E3:E24").Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[S M C]

Try this..

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("P:P,B:B")) Is Nothing Then
        With ThisWorkbook
            .Sheets("Sheet2").Range("E3:E24").Value = Application.Evaluate("=IF(ISBLANK(" & .Sheets("Sheet3").Range("E3:E24").Address(External:=True) & "),""""," & .Sheets("Sheet3").Range("E3:E24").Address(External:=True) & ")")
        End With
    End If
End Sub

 

[rockyw]

S M C, this still returns a 0

repairman615, I get a type mismatch error on this code. Not sure why. Do you see anything that need changed

 

[repairman615]

Hey Rockyw,

Give this a try:


<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Application.Intersect(Target, Range("P:P,B:B")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> ThisWorkbook<br>            <SPAN style="color:#00007F">If</SPAN> .Sheets("Sheet2").Range("E3:E24").Value <> "" Then _<br>            .Sheets("Sheet2").Range("E3:E24").Value = _<br>            .Sheets("Sheet3").Range("E3:E24").Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[rockyw]

This line returns an error

If .Sheets("Sheet2").Range("E3:E24").Value <> "" Then _

Now what this does is I paste a report on sheet 1, sheet 3 cells E3-E24 (now 26) has a formula that looks at the report on sheet one and adds the item numbers values to the cells, sheet 3 E3-E24. Then this code copys the cells on sheet 3 to sheet 2. I still get an error that says Run Time error 13, type mismatch. Thanks for the help

 

[S M C]

By blank cells, do you mean cells that return a double quote? Like so > ""

 

[S M C]

By the way, repairman, not sure how that works, maybe in Excel 2011 Mac it does (please confirm), but in Non-mac versions, I do not see how the code can run. you are trying to equate an array of values to "" which is not an array, but a single value. I'd be happy to stand corrected, but from what I know, that will always return an error.

Coming back to the OP, http://www.mrexcel.com/forum/member.php?u=135665since you haven't responded, not sure what the case is, but try this..

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("P:P,B:B")) Is Nothing Then
        With ThisWorkbook
            .Sheets("Sheet2").Range("E3:E24").Value = Application.Evaluate("=IF(" & .Sheets("Sheet3").Range("E3:E24").Address(External:=True) & "="""",""""," & .Sheets("Sheet3").Range("E3:E24").Address(External:=True) & ")")
        End With
    End If
End Sub

 

[repairman615]

SMC,

I am using 2007 pc.

I did not test it on data as I am really not sure what the OP is doing. I did check for compile errors and then posted.

It seemed like a good approach, but I see now what you have pointed out and agree the code I provided would not work as is.

The only other suggestion I have is to do a loop.

For each cell in the "Array"


Good Catch.

Jeff