How do I exit on blank cell?

[[email protected]]

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").Worksheets(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

[renegan]

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

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

[Jim Thomlinson]

Too many ""...

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

Jim Thomlinson


"[email protected]" 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").Worksheets(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
>
>