excel - Check if cell C1 is empty in all sheets - Stack Overflow

admin管理员组

文章数量:1405995

I tried to find the answer before writing my question, but without success.

I need to check if cell, let's say C1, is empty (no values) in all sheets in some excel file and I wan't to see message box with sheets names where C1 is not empty.

Reason for it is, I have file with lots of sheets and now I need to add some links, but not every list have same "form" and there are some list also which I don't need for it so I need to check those two things first.

So far I have this code, but something is wrong, because I always see message box with text: Cell C1 is empty in all sheets., even if I have some values in C1 (I add them in 3 sheets, because of testing this code).

Sub CheckCellC1()
    Dim ws As Worksheet
    Dim nonEmptyCell As String
    nonEmptyCell = ""
    
    ' Browse all sheets in the current workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Check if cell C1 is empty
        If ws.Range("C1").Value <> "" Then
            nonEmptyCell = nonEmptyCell & ws.Name
        End If
    Next ws
    
    ' Result
    If nonEmptyCell = "" Then
        MsgBox "Cell C1 is empty in all sheets."
    Else
        MsgBox "Cell C1 isn't empty in those sheets:" & nonEmptyCell
    End If
End Sub

What am I missing?

Thank you in advance for your help.

Update: I run my code from a PERSONAL.xlsb file and during "testing" I had open only one excel file.

Really only problem with my code was using ThisWorkbook instead of ActiveWorkbook. Adding & vbLf on the end of the line nonEmptySheets = nonEmptySheets & ws.Name & vbLf help a lot with message box imput, is easier to read now.

I'm beginner in VBA and always do mistakes like this (I have a bit problem to understand difference between "places" where code is and how it can impact code itself, but that is another topic), so thank you all for helping me with this.

I tried to find the answer before writing my question, but without success.

I need to check if cell, let's say C1, is empty (no values) in all sheets in some excel file and I wan't to see message box with sheets names where C1 is not empty.

Reason for it is, I have file with lots of sheets and now I need to add some links, but not every list have same "form" and there are some list also which I don't need for it so I need to check those two things first.

So far I have this code, but something is wrong, because I always see message box with text: Cell C1 is empty in all sheets., even if I have some values in C1 (I add them in 3 sheets, because of testing this code).

Sub CheckCellC1()
    Dim ws As Worksheet
    Dim nonEmptyCell As String
    nonEmptyCell = ""
    
    ' Browse all sheets in the current workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Check if cell C1 is empty
        If ws.Range("C1").Value <> "" Then
            nonEmptyCell = nonEmptyCell & ws.Name
        End If
    Next ws
    
    ' Result
    If nonEmptyCell = "" Then
        MsgBox "Cell C1 is empty in all sheets."
    Else
        MsgBox "Cell C1 isn't empty in those sheets:" & nonEmptyCell
    End If
End Sub

What am I missing?

Thank you in advance for your help.

Update: I run my code from a PERSONAL.xlsb file and during "testing" I had open only one excel file.

Really only problem with my code was using ThisWorkbook instead of ActiveWorkbook. Adding & vbLf on the end of the line nonEmptySheets = nonEmptySheets & ws.Name & vbLf help a lot with message box imput, is easier to read now.

I'm beginner in VBA and always do mistakes like this (I have a bit problem to understand difference between "places" where code is and how it can impact code itself, but that is another topic), so thank you all for helping me with this.

• excel
• vba

Share Improve this question Follow edited Mar 7 at 8:26 Cz_Libu asked Mar 6 at 14:22 Cz_LibuCz_Libu 2166 bronze badges 5

• 1 Does C1 have a formula that returns ""? Are you running this code from a PERSONAL.xlsb file or an .xlam add-in? – BigBen Commented Mar 6 at 14:24
• Isn't it more appropriate checking if Len(ws.Range("C1")) = 0? – Dominique Commented Mar 6 at 14:25
• 1 1) it depends on the definition of "empty", and 2) both that approach and OP's approach will fail with a type mismatch on a cell containing an error. Consider IsEmpty for checking if a cell is truly empty. – BigBen Commented Mar 6 at 14:29
• 2 nonEmptyCell = nonEmptyCell & ws.Name will give you an output something like Sheet1Sheet2Sheet3 add & vbcr to the end of that line to put each sheet name on a new row. – Darren Bartrup-Cook Commented Mar 6 at 14:44
• Give the user as much information as possible ie nonEmptyCell = nonEmptyCell & vbLf & ws.Name & vbTab & "|" & ws.Range("C1").Value & "|" – CDP1802 Commented Mar 6 at 15:21

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Check the Same Cell in All Worksheets

• Your code probably runs on the wrong workbook as suggested by BigBen in the comments.
• You could probably only change ThisWorkbook to ActiveWorkbook in your code and it would work.
• The following is no significant improvement on your code but contains a few tips in the comments.
• First run it as is, with ThisWorkbook. The message box should clarify what's happening since it lists the workbook path.

Sub CheckCellInSheets()
    
    ' Define constants.
    Const CELL_ADDRESS As String = "C1"
    Const DELIMITER As String = vbLf ' line feed ('next row')
    
    ' Use only one of the following 3 (probably 3):
    
    ' 1.) Reference the workbook containing this code.
    ' You cannot use this if the code is in another workbook!
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    ' 2.) Reference the workbook by its name.
    ' Used when the code is in another workbook and you know
    ' its name and that it is open!
    'Dim wb As Workbook: Set wb = Workbooks("MyBook.xlsx")
    
    ' 3.) Reference the active workbook, the one you're looking at.
    ' Used when the workbook could have any name
    ' and the code is in another workbook!
    ' The code can often be run on any workbook!
    'Dim wb As Workbook: Set wb = ActiveWorkbook ' active workbook
    
    ' Declare additional variables.
    Dim ws As Worksheet, NonBlankSheetList As String, IsFirstFound As Boolean
    
    ' Loop through all worksheets of the workbook.
    For Each ws In wb.Worksheets
        ' Check if the cell is blank.
        ' 'CStr' prevents error if error value in cell.
        If CStr(ws.Range(CELL_ADDRESS).Value) <> "" Then
        ' Check if the cell is (truly) empty.
        'If IsEmpty(ws.Range(CELL_ADDRESS).Value) Then
            If IsFirstFound Then ' all after first
                NonBlankSheetList = NonBlankSheetList & DELIMITER & ws.Name
            Else ' first
                NonBlankSheetList = ws.Name
                IsFirstFound = True
            End If
        End If
    Next ws
    
    ' Inform.
    If IsFirstFound Then
        MsgBox "Cell """ & CELL_ADDRESS _
            & """ isn't blank in the following sheets of workbook """ _
            & wb.FullName & """:" & vbLf & vbLf _
            & NonBlankSheetList, vbInformation
    Else
        MsgBox "Cell """ & CELL_ADDRESS _
            & " is blank in all sheets of workbook """ _
            & wb.FullName & """!", vbExclamation
    End If

End Sub

本文标签： excelCheck if cell C1 is empty in all sheetsStack Overflow