Private Function HandleNullReads(ByRef ftpWkBk As Workbook, _
ByRef exceptArray() As String, _
ByRef exceptFile As String, _
ByRef sendDate As String) As String
Dim ftpWkSht As Worksheet
Dim filtSheet As Worksheet
Dim ftpRange As Range
Dim filtRange As Range
Dim filtMax As Long
Dim ftpMax As Long
[COLOR=#006400][B]Dim ftpMaxCol As Long[/B][/COLOR]
Dim meterRdCol As Long
Dim i As Long
Dim fltShtName As String
Dim failReturn As String
Dim errString As String
fltShtName = "Filtered"
meterRdCol = 6
[B][COLOR=#006400]ftpMaxCol = 10[/COLOR][/B]
' Up front prep of FTP workbook and sheet
If Not ftpWkBk Is Nothing Then
ftpWkBk.Activate
Set ftpWkSht = ftpWkBk.Sheets(1)
If Not ftpWkSht Is Nothing Then
HandleNullReads = "Success"
With ftpWkSht
' If we have a valid workbook and a valid worksheet, find the bottom row
ftpMax = .UsedRange.Rows.Count
Set ftpRange = .Range(.Cells(1, 1), .Cells(ftpMax, ftpMaxCol).End(xlUp))
With ftpRange
.AutoFilter
.AutoFilter field:=meterRdCol, Criteria1:=vbNullString ' or "="
' Make a new sheet and copy filtered data to it to be able to address each row
.SpecialCells(xlCellTypeVisible).Copy
Set filtSheet = Sheets.Add(After:=Sheets(1))
filtSheet.name = fltShtName
filtSheet.Paste
Application.CutCopyMode = False ' Clear clipboard of copied data
With filtSheet
Set filtRange = filtSheet.Range("A2:J" & filtSheet.Range("A65535").End(xlUp).Row)
filtMax = filtRange.Range("A65535").End(xlUp).Row
If filtMax > 1 Then ' Only header row, don't process
With filtRange
For i = 1 To filtMax
If .Range("C" & i) = "Inspection Completed" Then
'Add to exceptions report and remove - no Inspection Completed with a _
NULL meter reading is valid
failReturn = WriteException(.Range("A" & i).Value, exceptArray)
errString = .Range("A" & i) & " 'Inspection Completed' without a meter reading"
failReturn = ProblemReport(errString, exceptFile, sendDate)
Else
If .Range("J" & i) Is Nothing Then ' Check for valid 'Obstructed Code'
' Add to exceptions report
' failReturn = WriteException(.Range("A" & i).Value, exceptArray)
errString = .Range("A" & i) & " 'Obstructed Meter' without a meter reading"
errString = errString & " or 'Obstructed Code'"
failReturn = ProblemReport(errString, exceptFile, sendDate)
End If
End If
Next i
End With ' filtRange
Else
Application.DisplayAlerts = False ' Suppress "SaveAs" dialog box
Application.EnableEvents = False ' Suppress BeforeSave event
filtSheet.Delete
Application.EnableEvents = True
Application.DisplayAlerts = True
ftpRange.AutoFilter
Set filtSheet = Nothing
Set ftpWkSht = Nothing
Set ftpRange = Nothing
Workbooks("DailyFTP.xlsm").Sheets("DailyFTP").Activate
Exit Function
End If ' filtMax > 1
End With ' filtSheet
End With ' ftpRange
End With ' ftpWkSht
ActiveSheet.AutoFilterMode = False
Application.DisplayAlerts = False ' Suppress "SaveAs" dialog box
Application.EnableEvents = False ' Suppress BeforeSave event
ftpRange.AutoFilter
filtSheet.Delete
Application.EnableEvents = True
Application.DisplayAlerts = True
Set filtSheet = Nothing
Set ftpWkSht = Nothing
Set ftpRange = Nothing
Set filtRange = Nothing
Else
errString = "NULL meter read worksheet object is not set"
failReturn = ProblemReport(errString, exceptFile, sendDate)
HandleNullReads = errString
End If
Else
errString = "NULL meter read workbook object is not set"
failReturn = ProblemReport(errString, exceptFile, sendDate)
HandleNullReads = errString
End If
Workbooks("DailyFTP.xlsm").Sheets("DailyFTP").Activate
End Function ' HandleNullReads
