Using Sheet.Cells Property with a Range

[business_analyst]

Hell All,

So I would like to look through a range of cells on a sheet to check if they have numbers in them. Basically it is a sheet with 4 columns (A through D). And 10 rows (2 through 11). I would like to loop through all four columns, and check that same row range for each column. The code below does this for the first row of each column. But how to I check all the rows for each column? I cannot insert a range into the sheet1.cells property can I? And I do not know how to use the range property with cell indexes. Any help would be greatly appreciated.

dim row as integer
dim col as integer

col = 1
row = 2

For col = 1 to 4
    If sheet1.cells(row, col) = 0 then
        Msgbox "The range is equal to 0"
    end if
next col

 

[Colin Legg]

I think you are looking for a nested loop?

Sub foo()
 
    Dim lRow As Long
    Dim lCol As Long
 
    For lRow = 2 To 11
        For lCol = 1 To 4
 
            If Sheet1.Cells(lRow, lCol).Value = 0 Then
                MsgBox "The range is equal to 0"
            End If
 
        Next lCol
    Next lRow
 
End Sub

 

[VoG]

Perhaps

Dim irow As Integer
Dim icol As Integer


For icol = 1 To 4
    For irow = 2 To 11
        If Sheet1.Cells(irow, icol).Value = 0 Then
            MsgBox Sheet1.Cells(irow, icol).Address(False, False) & " is equal to 0"
        End If
    Next irow
Next icol

 

[business_analyst]

Thanks, a nested loop would make sense if I was looking for each individual cell, but I was actually trying to see if the sum of all the rows in each column are equal to 0. For example, with column A, if row 2 through row 10 is equal to 0 then the user would be prompted with a message. Essentially, they should be allowed to enter a number in at least one cell in each column. Sorry if I didn't clarify that originally.

 

[VoG]

You could use

worksheetfunction.sum(cells(irow,icol).resize(10))

to calculate the sum of values in column icol.