PDA

View Full Version : Checking for blank cells



vbid
07-11-2017, 01:55 AM
Sub Checkforblankcells()
Dim ws As Worksheet
For j = 14 To 47
For i = 2 To 35
For Each ws In Worksheets

If ws.Name <> "control" Or ws.Name <> "Linkslist" Then

If ws.Range("P" & i) <> "" Then
Sheets("control").Range("M" & j) = "OK"
Else
Sheets("control").Range("M" & j) = "NOT OK"
End If
End If
Next
Next
Next
End Sub

I would like to check if there are empty cells in range "P2:P35" for multiple worksheets. I have a blank cell in 1 of my worksheet but the macro did not generate a "NOT OK" result and I would like to know how should i fix the code to generate a "NOT OK" in my result

mana
07-11-2017, 03:01 AM
Else
Sheets("control").Range("M" & j) = "NOT OK"
Exit sub
End If

snb
07-11-2017, 03:01 AM
Pleas use code tags.


Sub M_snb()
on error resume next

for each it in sheets
x3=it.range("P2:P35").specialcells(4)
if err.number=0 then exit for
err.clear
next

msgbox it.name
End Sub

vbid
07-11-2017, 05:53 PM
Sub M_snb()
Dim ws As Worksheet

On Error Resume Next
For Each it In Sheets

If ws.Name <> "control" Or ws.Name <> "linkslist" Then

x3 = it.Range("P2:P35").SpecialCells(4)
If Err.Number = 0 Then Exit For
Err.Clear
Else
End If
Next
MsgBox it.Name
End Sub

Thank you both for your replies. However, the codes does not seem to work for me.
Mana: Your code just generates a "NOT OK" for the sheet("control") and it stops afterwards.

Snb: Your code works but it does not detect the blank cell within the range. I guess your code detects the whole range and if other cells are filled, it will just run through without the message box popping up. How do i use code tags?


Sub M_snb()
Dim ws As Worksheet

On Error Resume Next
For Each it In Sheets

If ws.Name <> "control" Or ws.Name <> "linkslist" Then

x3 = it.Range("P2:P35").SpecialCells(4)
If Err.Number = 0 Then Exit For
Err.Clear
Else
End If
Next
MsgBox it.Name
End Sub

Thank you for both your replies

mdmackillop
07-12-2017, 02:43 AM
How do i use code tags?
Highlight your code and select the # symbol above or select the symbol then paste your code.

mdmackillop
07-12-2017, 03:05 AM
Sub Test()
Dim it As Worksheet
Dim i As Long
Dim x3 As Range

On Error Resume Next
With Sheets("Control")
For Each it In Worksheets
If it.Name <> "Control" And it.Name <> "linkslist" Then
Set x3 = it.Range("P2:P35").SpecialCells(4)
i = i + 1
.Cells(i, 1) = it.Name
If x3 Is Nothing Then
.Cells(i, 2) = "OK"
Else
.Cells(i, 2) = "Not OK"
.Cells(i, 3) = x3.Address
End If
Else
End If
Next
End With
End Sub

snb
07-12-2017, 03:48 AM
Please study the code.
If a blank cell has been found it shows the name of the sheet where it appears.

If a cell containing a formula results in a 'blank' cell it isn't a 'blank' cell: .specialcells(4) <>.specialcells(-4123)


Ergo: You should always provide a sample workbook.