MrExcel.com - Your One Stop for Excel Tips & Solutions

Performing a sub only if cells A100-Z100 are all filled in: how then?

Posted by Remi on February 01, 2002 5:29 AM

Hi,

I want excell to perform a routine but only of cells A100-Z100 (so, all of them) heve someting in them.
If 1 of them is empty: the routine should not take place.

I`ve worked out the following programming: but the
"If Not IsEmpty" statement seems not to work. I do not get any errors but if I leave one or more cells in the concerning range empty the sub scanpast()takes place anyway.

Can anyone see what`s the problem?

Thanks a lot people!
Remi
Holland.


**********************************************
Sub workbook_beforeclose(cancel As Boolean)

If Not IsEmpty(A100 And B100 And E100 And H100 And I100 And J100 And K100 And L100 And O100 And S100 And T100 And U100 And V100 And W100 And X100 And Y100 And Z100) Then

Range("A100:Z100").Select
Selection.Copy
scanpast
Else: GoTo einde
End If
Exit Sub
einde:
MsgBox "Niet alle gegevens zijn ingevuld!!", vbCritical, "Niet compleet"
End Sub

Sub scanpast()
Dim r As Long
Range("A1").Activate
For r = 1 To 65536
If Not IsEmpty(ActiveCell) Then
ActiveCell.Offset(1, 0).Activate
Else
If IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(0, 1)) And _
IsEmpty(ActiveCell.Offset(0, 2)) And IsEmpty(ActiveCell.Offset(0, 3)) And _
IsEmpty(ActiveCell.Offset(0, 4)) And IsEmpty(ActiveCell.Offset(0, 5)) And _
IsEmpty(ActiveCell.Offset(0, 6)) And IsEmpty(ActiveCell.Offset(0, 7)) And _
IsEmpty(ActiveCell.Offset(0, 8)) And IsEmpty(ActiveCell.Offset(0, 9)) _
Then
ActiveSheet.Paste
ActiveWorkbook.Save

Exit For
Else
ActiveCell.Offset(1, 0).Activate
End If
End If
Next r

End Sub
***********************************

Posted by JohnG on February 01, 2002 6:06 AM

Try this
Sub IsEmptyChk()
Dim Floop As Integer

For Floop = 1 To 26 'A to Z
If IsEmpty(Cells(100, Floop).Value) = True Then
MsgBox "empty value found at " & Cells(100, Floop).Address
Exit For
End If
Next Floop
End Sub

Posted by DK on February 01, 2002 6:13 AM

Hello,

How about this?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lc As Long, blnCellsFilled As Boolean

'Check to see if any cells are blank in A100:Z100
blnCellsFilled = True
For lc = 1 To 26
If Len(Cells(100, lc)) = 0 Then blnCellsFilled = False
Next

If blnCellsFilled = True Then
Range("A100:Z100").Select
Selection.Copy
scanpast
Else
MsgBox "Niet alle gegevens zijn ingevuld!!", vbCritical, "Niet compleet"
End If
End Sub

When you use this statement :-

If Not Isempty (A100 And B100)...

VBA will think that A100 and B100 are variables, not cells. You need to make sure Excel knows that this is a range by using either Range("A100") or the shortcut [A100]. HOWEVER, this still won't work because And is a keyword used for performing logical operations (search for And in the VBA help).

HTH,
D

Posted by Remi on February 05, 2002 2:55 AM

THIS IS JUST GREAT, THNAKS YOU ALL FOR YOUR HELP.
REMI