VBA function returns correctly a value the first time, but not the second time it runs

Question

So when I run this I just get the number of rows in total instead of a count of the empty rows only. It worked once, but then it keeps giving me back the total from B4 until LastRowNumber. Can't figure out why..

Function CheckBlankCells(worksheetName As String)
  nrows = 0
  Set ws = ThisWorkbook.Worksheets(worksheetName)
  LastRowNumber = ws.Cells(Cells.Rows.Count, "B").End(xlUp).Row
  
  For Each cell In ws.Range("B4:B" & LastRowNumber)
    If Not IsEmpty(cell) Then
      If Range("C" & cell.Row) = "" Or IsEmpty(Range("C" & cell.Row)) Then
          nrows = nrows + 1
      End If
    End If
  Next
  CheckBlankCells = nrows
End Function

I also tried to do:

If Not IsEmpty(cell) And IsEmpty(Range("C" & cell.Row))

But with the same result.

question from:https://stackoverflow.com/questions/65901610/vba-function-returns-correctly-a-value-the-first-time-but-not-the-second-time-i

Answer 1

As SJR suggested, I missed ws before the Ranges. So I had to change this bit:

If Range("C" & cell.Row) = "" Or IsEmpty(Range("C" & cell.Row)) Then

to this on line 8 of the function:

If ws.Range("C" & cell.Row) = "" Or IsEmpty(ws.Range("C" & cell.Row)) Then