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

 

[S M C]

Acknowledge that Jeff. Cheers mate

 

[rockyw]

I have Excel 2007, windows 7 64 bit. If you have any other suggestions I would love to try it. Thanks

 

[rockyw]

For the return it just says 0 The condisional formating sees the 0 as a value and shows the text. If there is no value, the text stays white on a white background and I can not see it. Thanks

 

[S M C]

Well, maybe uploading your file in any of the share sites and posting a link here might help.

 

[repairman615]

After some thought and help from SMC, I think now this may do:

<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">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> .Sheets("Sheet3").Range("E3:E24")<br>            <SPAN style="color:#00007F">If</SPAN> c.Value <> 0 Then _<br>                Sheets("Sheet2").Range("E" & c.Row).Value = _<br>                .Sheets("Sheet3").Range("E" & c.Row).Value<br>            <SPAN style="color:#00007F">Next</SPAN> c<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>

-Jeff

 

[rockyw]

Hey repairman! you fixed that!! Thanks it seems to work great. Now as more items numbers are added I will need to expand the range, E3 - E 26 ect. What in his code should I change to expand the range?

 For Each c In .Sheets("Sheet3").Range("E3:E26")
            If c.Value <> 0 Then _
                Sheets("Sheet2").Range("E" & c.Row).Value = _
                .Sheets("Sheet3").Range("E" & c.Row).Value
            Next c

Will changing to a 26 like I did here expand the range to E3 - E26? Thanks for the code.

 

[repairman615]

Glad to hear!

I did not test this next bit, but it is along the lines of something that should work for that.

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">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> .Sheets("Sheet3").Range("E3:E" & _<br>                    Range("E" & Rows.Count).End(xlUp).Row)<br>                <SPAN style="color:#00007F">If</SPAN> c.Value <> 0 Then _<br>                    Sheets("Sheet2").Range("E" & c.Row).Value = _<br>                    .Sheets("Sheet3").Range("E" & c.Row).Value<br>            <SPAN style="color:#00007F">Next</SPAN> c<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]

So this code does not need edited to add rows? Thanks so much.

 

[repairman615]

Hello Rockyw,

Sorry I meant to clarify this more...

Yes you were right about being able to expand the code with the 'from 24 to 26' (hard-code). The newer code should automatically do that by looking for the last row for you.


Unless sheet 3 is not where the row count needs to happen, then yes
then the latest code would automatically adjust or be dynamic and would not need editing to expand rows.

If the row count should need to happen according to sheet2 then some adjustment will be needed.



What this is doing is using sheet3 range E3:E and the last row used.

Example= If the last row used on sheet3 in column E:E was row 24, it would loop through E3:E24 or if it was row 26 then E3:E26 and so forth.


Hope I have cleared any questions you had.

Let me know if the latest code seemed to work or if it needed any changes. I will be glad to help to the best of my ability.

Jeff