Data check

[Not.yet]

Hi, I would check data table. There are 150 columns about 3600 rows. I
need to check if data field is missing value. If yes, so error message
must be generated. Doesn't anybody know to help me with macro? Thanks
Pavel

 

[Mike H.]

Sub CheckData
Dim X as double
Dim Y as double
Dim ErrorData(50000,2) as variant
Dim Fnd as double
for X=1 to 3600
for Y=1 to 150
if cells(x,y).value=empty then
Fnd=Fnd+1
ErrorData(Fnd,1)=X
ErrorData(Fnd,2)=Y
end if
Next
Next
if Fnd>0 then
Dim MyEntries As String
Workbooks.Add Template:="Workbook"
MyEntries = ActiveWorkbook.Name
Cells(1,1).value="Bad Row"
Cells(1,2).value="Bad Col"
for X=1 to Fnd
cells(x+1,1)=ErrorData(X,1)
cells(x+1,2)=ErrorData(x,2)
Next
End if
'then add code for printing or whatever....
End sub

 

[steonken]

hi Pavel,
missing value means a blank cell? then try this macro:

Sub GetBlankCells()
On Error Resume Next
Set rng = Range(ActiveSheet.UsedRange.Address). _
SpecialCells(xlCellTypeBlanks)
If Err.Number > 0 Then
MsgBox "no blanks"
Else
MsgBox "blanks in " & rng.Address
End If
End Sub

bye
stefan