Macro using if

rtuck · Feb 28th, 2002, 05:07 AM

I have a macro recorded to remove data from the worksheet, paste it elsewhere, clear the original data and let the user strat from scratch again.

I need to place a code before the main bulk of the macro to stop it running if any of the 15 or so cells is empty. There was a similar question but it referred to one cell only, have tried to manipulate it but keep getting errors.

Cheers

Mudface · Feb 28th, 2002, 05:15 AM

If your range is in, say A1:A15 then the following could be suitable: -

Dim c As Range
Dim myRng As Range

Set myRng = ActiveSheet.Range("A1:A15") ' Change your range here to suit
For Each c In myRng
If IsEmpty(c) Then MsgBox "Cell " & c.Address & " is empty. Terminating": Exit Sub
Next c

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 28th, 2002, 05:07 AM	#1
rtuck New Member Join Date: Feb 2002 Posts: 3	I have a macro recorded to remove data from the worksheet, paste it elsewhere, clear the original data and let the user strat from scratch again. I need to place a code before the main bulk of the macro to stop it running if any of the 15 or so cells is empty. There was a similar question but it referred to one cell only, have tried to manipulate it but keep getting errors. Cheers

Feb 28th, 2002, 05:15 AM	#2
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	If your range is in, say A1:A15 then the following could be suitable: - Dim c As Range Dim myRng As Range Set myRng = ActiveSheet.Range("A1:A15") ' Change your range here to suit For Each c In myRng If IsEmpty(c) Then MsgBox "Cell " & c.Address & " is empty. Terminating": Exit Sub Next c