Ignore "0" values or Empty cells

[Iceshade]

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

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

 

[oldbrewer]

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