Return cell address of empty cells (required fields) in message box

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
Hi - I am using the following code to search column D for either "Task Description" or "Method of Quoting" and when it finds either of those words, it looks in column E. If column E is empty then it returns an error message box.

Does anyone know if its possible to return the cell address of those blank cells within my message box? I haven't been able to come up with anyting


Code:
Sub ShowMissingTextWarning()
'Definitions
Dim FndCell As Range, arr As Variant
On Error Resume Next
For Each arr In Array("Task Description:", "Method of Quoting:")
    Set FndCell = Range("D:D").Find(arr, , xlValues, xlWhole).Offset(0, 1)
    If FndCell.Value = vbNullString Then
        If Err.Number = 0 Then
            MsgBox arr & " is missing!", vbExclamation
            FndCell.Select
        End If
    End If
Next arr
On Error GoTo 0
End Sub
 
For example:

Code:
Sub ShowMissingTextWarning()    'Definitions
    Dim FndCell As Range, arr As Variant, rngResult As Range
    
    On Error Resume Next
    
    For Each arr In Array("Task Description:", "Method of Quoting:")
        Set FndCell = Columns("D").Find(arr, , xlValues, xlWhole).Offset(, 1)
        If FndCell.Value = "" Then
            If rngResult Is Nothing Then
                Set rngResult = FndCell
            Else
                Set rngResult = Union(FndCell, rngResult)
            End If
        End If
    Next
    
    If Not rngResult Is Nothing Then
        MsgBox rngResult.Address(0, 0)
    End If
    
    On Error GoTo 0
    
End Sub
 
Upvote 0
Try this:

Code:
Sub ShowMissingTextWarning()
'Definitions
Dim FndCell As Range, arr As Variant
For Each arr In Array("Task Description:", "Method of Quoting:")
    Set FndCell = Range("D:D").Find(arr, , xlValues, xlWhole)
    If FndCell Is Nothing Then
        MsgBox arr & " is missing!", vbExclamation
    ElseIf Replace(FndCell.Offset(0, 1).Value, " ", "") = "" Then
        MsgBox "Cell " & FndCell.Offset(0, 1).Address(0, 0) & " is empty!", vbExclamation
        FndCell.Offset(0, 1).Select
    End If
Next arr
End Sub
 
Upvote 0
Thank you! Unfortunately, it is only returning the first (and I guess second) occurrence. The first 2 cells that meet the criteria are E31 and E32. The result in msg box was E31:E32.

But, it should also return E41, E42, E51, E52, etc.

Also, if possible instead of "E31:E32" is it possible to do "E31, E32, E41, E42, E51, and E52" ???
 
Upvote 0
Then I would suggest to use the function FindAll by Chip Pearson:
FindAll VBA Function

After that, loop over de cells found (example code in his article) and see if the cell to the right is empty.
Collect the address in a string like:

Code:
s = s & ", " & r.Offset(,1).Address(0,0)

such that the variable s is the concatenation of the cell addresses.
 
Upvote 0
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top