How do I exit on blank cell?
 

I have a workbook with a ton of sheets. One macro calls the below
function and runs the below for each sheet, but bombs out when it hits
a sheet that is blank. What I would like to do is exit the below macro
once it reaches a sheet that has cell B4 as blank. I'm sure the code is
horrible (I'm a beginner), but I'm not sure why it doesn't work. It
just keeps going without exiting. Help please and thank you!?!?!


Sub Import_Files()

If Range("B4").Value = """" Then
Exit Sub
End If
Dim WB As Workbook
Set WB = Workbooks.Open(Filename:="N:\US\Index Investments\Equity
Management and Training\Index Research Group\Shared\Audit PCF Files\" &
Range("B4").Value)
'should try this. stupid vba so constricting
WB.Worksheets(1).Range("A1:E4").Copy
Destination:=Workbooks("Audit_Template").Worksheet s(3).Range("A6")
Range("A1:E45000").Select
Selection.Copy
Application.DisplayAlerts = False
WB.Close Savechanges:=False
Application.DisplayAlerts = True
Range("A6").Select
ActiveSheet.Paste
Range("A6").Select
Application.CutCopyMode = False
Range("F7").Select
ActiveCell.FormulaR1C1 = "=RC[-5]=R1C2"
Range("F7").Select
Selection.AutoFill Destination:=Range("F7:F45000")
Range("F7:F45000").Select
Range("A6:F6").Select
Range("F6").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:="FALSE"
Rows("7:7").Select
Rows("7:45000").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=6, Criteria1:="TRUE"
Columns("F:F").Select
Selection.AutoFilter Field:=6
Selection.Delete Shift:=xlToLeft
Selection.AutoFilter
Range("A6").Select

End Sub

How do I exit on blank cell?
 

Range("B4").Value = """"

Since you use Value function, you shouldn't enter the value as """".
Just enter "" then it should work.


--
renegan
------------------------------------------------------------------------
renegan's Profile: http://www.excelforum.com/member.php...o&userid=10450
View this thread: http://www.excelforum.com/showthread...hreadid=538648

How do I exit on blank cell?
 

Too many ""...

If Range("B4").Value = "" then exit sub
--
HTH...

Jim Thomlinson


" wrote:

I have a workbook with a ton of sheets. One macro calls the below
function and runs the below for each sheet, but bombs out when it hits
a sheet that is blank. What I would like to do is exit the below macro
once it reaches a sheet that has cell B4 as blank. I'm sure the code is
horrible (I'm a beginner), but I'm not sure why it doesn't work. It
just keeps going without exiting. Help please and thank you!?!?!


Sub Import_Files()

If Range("B4").Value = """" Then
Exit Sub
End If
Dim WB As Workbook
Set WB = Workbooks.Open(Filename:="N:\US\Index Investments\Equity
Management and Training\Index Research Group\Shared\Audit PCF Files\" &
Range("B4").Value)
'should try this. stupid vba so constricting
WB.Worksheets(1).Range("A1:E4").Copy
Destination:=Workbooks("Audit_Template").Worksheet s(3).Range("A6")
Range("A1:E45000").Select
Selection.Copy
Application.DisplayAlerts = False
WB.Close Savechanges:=False
Application.DisplayAlerts = True
Range("A6").Select
ActiveSheet.Paste
Range("A6").Select
Application.CutCopyMode = False
Range("F7").Select
ActiveCell.FormulaR1C1 = "=RC[-5]=R1C2"
Range("F7").Select
Selection.AutoFill Destination:=Range("F7:F45000")
Range("F7:F45000").Select
Range("A6:F6").Select
Range("F6").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:="FALSE"
Rows("7:7").Select
Rows("7:45000").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=6, Criteria1:="TRUE"
Columns("F:F").Select
Selection.AutoFilter Field:=6
Selection.Delete Shift:=xlToLeft
Selection.AutoFilter
Range("A6").Select

End Sub