Ignore "0" values or Empty cells

Iceshade

Board Regular
Joined
May 22, 2017
Messages
104
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,

I have a code that will go and source data from a specific range all worksheets within the workbook.

I cannot seems to get it to ignore "0" values or empty cells. Tried a few things but keep getting "Next without For" errors. I am quite a novice to VBA so could really use some guidance here.

Here is my code. Basically want the myCell.value to not include "0" results

Code:
ub Summary_All_Worksheets_With_Formulas()
    Dim Sh As Worksheet
    Dim Req As Worksheet
    Dim myCell As Range
    Dim ColNum As Integer
    Dim RwNum As Long
    Dim Basebook As Workbook
        With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
   
    'Add a worksheet with the name "Requirements Gathering"
    Set Basebook = ThisWorkbook
    Set Req = Worksheets("Requirements Gathering")
    'The links to the first sheet will start column 1
    ColNum = 1
    
    For Each Sh In Basebook.Worksheets
        If Sh.Name <> Req.Name And Sh.Visible Then
            RwNum = 11
            ColNum = ColNum + 1
            Columns("C:C").Insert , CopyOrigin:=xlFormatFromLeftOrAbove
                        
            'Get the values
            Req.Cells(RwNum, ColNum).Value = Sh.Name
                For Each myCell In Sh.Range("Q2,Q3,Q4")
                RwNum = RwNum + 1
                Req.Cells(RwNum, ColNum).Formula = _
                "='" & Sh.Name & "'!" & myCell.Address(False, False)
                Req.Cells.NumberFormat = "General"
                Next myCell
        End If
     
    Next Sh
    
    Req.UsedRange.Columns.AutoFit
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
    
End Sub
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
For Each myCell In Sh.Range("Q2,Q3,Q4")
if mycell=0 or mycell="" then goto 999
RwNum = RwNum + 1
Req.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Req.Cells.NumberFormat = "General"
999 Next myCell
End If

Next Sh
 
Upvote 0

Forum statistics

Threads
1,223,250
Messages
6,171,036
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top