CODEWITHSUNDEEP
vbaexcel
Alonso4077
Alonso4077

Reputation: 27

Using If then for entire workbook to look at averages in vba

I'm trying to create an If then macro that will allow me to search the entire workbook and bring back the name of the worksheets in cells(PRow,3) with values less than or equal to 5 in range ("B26").value

So far, the one I have doesn't seem to bring back all the results that meet the criteria.

Dim PRow As Long
   PRow = 8
     With wsAddPatient
         For Each ws In ActiveWorkbook.Worksheets
            If ws.Range("B26").Value <= 5 _
                And ws.Range("B26").Value <> "" Then
                    .Cells(PRow, 3).Value = ws.Name
                     PRow = PRow + 1
            End If
        Next
    End With

Upvotes: 1

Views: 34

Answers (1)

user4039065
user4039065

Reputation:

Don't try to test the numerical value of a blank cell or one that contains text.

Dim PRow As Long
PRow = 8
    With wsAddPatient
        For Each ws In ActiveWorkbook.Worksheets
            If IsNUmeric(ws.Range("B26").Value2)  Then
                If ws.Range("B26").Value2 <= 5  Then
                    .Cells(PRow, 3) = ws.Name
                    PRow = PRow + 1
            End If
        End If
    Next
End With

Make the test for a numeric cell value separate from any other test as multiple tests will all be evaluated.

This should have resulted in a Type mismatch error is you were trying to compare text; do not use On Error Resume Next until you are confident that you have covered all possibilities with proper error control.

Upvotes: 2

Related Questions