VBA检查在给定范围内是否有任何单元格不为空 [英] VBA to check if any cell is not blank in given range

问题描述

我有以下代码，当保存工作簿之前，当特定工作表为空时，将引发提示.

I have the below code that will throw a prompt when a particular sheet is empty before saving the workbook.

代码用途:要检查，是否在主表中下拉列表的值为是"，而如果为是"，则检查特定表上的给定范围是否为空白.如果为是"，则提示并在主表上将下拉值更改为否".

Purpose of code: To check, if value of drop-down is "yes" in Main Sheet and if "yes", check if given range on a particular sheet is blank. If "yes", throw a prompt and change the drop down value to "No" on main sheet.

关注点:代码中的 For循环将检查给定范围内是否有任何单元格为空，相反，我希望代码检查给定范围内的任何一个单元格中是否有任何条目.可以说给定范围是 E10:G19 ，如果我们在E10中有一个条目，它应该从代码中出来并且不抛出提示，并且仅当给定范围中的所有单元格都为空时才抛出

Concern: For loop in the code will check if any cell is empty in given range, instead, I want a code to check if there is an entry in any one cell in given range. Lets say given range is E10:G19, if we have an entry in E10, It should come out of the code and should not throw a prompt and should throw only if all the cells in given range is empty.

问题:什么可以代替可以满足我的目的的 For循环?

Question: What should replace my For loop that can serve my purpose?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet

Dim Rvalue As Range
Dim cell As Range
Set Rvalue = Sheets("Uni-corp").Range("E10:G19")

If Worksheets("Main").Range("E29").Value = "YES" Then
For Each cell In Rvalue
        If IsEmpty(cell) Then
            bOk = True
            Exit For
        Else: bOk = False
        End If
Next

If bOk Then
    If MsgBox("Sheet is blank", vbOKCancel + vbInformation) = vbOK Then
    Worksheets("Main").Range("E29").Value = "NO"
    Cancel = True
    End If
End If
End If
End Sub

推荐答案

也许您正在追求这样的东西:

Maybe you're after something like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)    
    If UCase(Worksheets("Main").Range("E29").Value) <> "YES" Then Exit Sub
    If WorksheetFunction.CountA(Worksheets("Uni-corp").Range("E10:G19")) > 0 Then Exit Sub
    If MsgBox("Sheet is blank", vbOKCancel + vbInformation) = vbOK Then
        Worksheets("Main").Range("E29").Value = "NO"
        Cancel = True '<--| this will make the macro not save the workbook
    End If
End Sub

这篇关于VBA检查在给定范围内是否有任何单元格不为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！