PDA

View Full Version : Solved: IsNull Function Not Functioning



jo15765
01-26-2012, 04:22 PM
I have this function:

Public Sub Check_Null()
Dim Rng As Range
Dim wb As Workbook

Set Rng = Range("A2:A3")

If IsNull(Rng) = True Then
wb.Close
Else
wb.SaveAs FileName:="Location & FileName"
End If
End Sub

Which I am trying to use if cells A2 and A3 are Null, then close the workbook, there's no point in saving. However, if there is data in A2 and A3 then I want to save the workbook.

Problem I am having is it is closing the workbook regardless if there is data in A2 and A3. It just closes the workbook regardless!

This may be pertinent may not be, A2 and A3 hold numbers for data.

Bob Phillips
01-26-2012, 05:11 PM
Public Sub Check_Null()
Dim Rng As Range
Dim wb As Workbook

Set Rng = Range("A2:A3")

If Application.CountA(rng) = 0 Then
wb.Close
Else
wb.SaveAs FileName:="Location & FileName"
End If
End Sub

jo15765
01-26-2012, 06:52 PM
Same situation as before...regardless of whether or not there is data it closes. Here is the code I use:

Public Sub Check_Null(varBook)
Dim Rng As Range
Dim wb As Workbook

Set Rng = Range("A2:A3")

If Application.CountA(Rng) = 0 Then
wb.Close
Else
If Left(varBook, 2) = "MT" Then
wb.SaveAs Filename:="C:\Files\Daily\" & WB.Name
End If
End If
End Sub
Public Sub Testing()
Dim varBook
Dim varBooks

VarBooks = Array("Mtn", "Lake", "River")

For each varBook in Varbooks
Set wb = Workbooks.Open(Filename:="R:\Michael\Data\ & varBook)
End If
If Not wb Is Nothing Then
Dim wks As Worksheet, qt As QueryTable
For Each wks In wb.Worksheets
For Each qt In wks.QueryTables
qt.Refresh BackgroundQuery:=False
Next qt
Next wks
Set qt = Nothing
Set wks = Nothing

Application.DisplayAlerts = False
Run "Sort_Date"
Call Is_Blank(varBook)
ActiveWorkbook.Close
End If
On Error GoTo 0
Next varBook

End Sub
Public Sub Sort_Date_()
Dim res As Variant
Rows("1:1075").Select
Range("A1075").Activate
res = Application.Match("Date", Rows(1), 0)
If Not IsError(res) Then
Selection.Sort Key1:=Cells(9, res), Order1:=xlDescending, HEADER:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
Range("A2").Select
End Sub

Bob Phillips
01-27-2012, 09:34 AM
I have just run that procedure with and without data in A2:A3, and it takes the correct path in each case.

But looking more closely at your code, where in Check_Null does the variable wb get set? Nowhere that I can see.

jo15765
01-27-2012, 10:50 AM
Your right...Next question then, if I change Check_Null to:

Public Sub Check_Null(varBook)
Dim Rng As Range
Dim wb As Workbook

Set WB = ActiveWorkbook

Set Rng = Range("A2:A3")

If Application.CountA(Rng) = 0 Then
wb.Close
Else
If Left(varBook, 2) = "MT" Then
wb.SaveAs Filename:="C:\Files\Daily\" & WB.Name
End If
End If
End Sub


I set WB = ActiveWorkbook, and now of course it runs just like it should! Thank you for your help, and patience!