CODEWITHSUNDEEP
vbaexcel
LillieG
LillieG

Reputation: 75

Excel VBA: message box to show list of errors at the end

I am new to VBA and I would really appreciate your help with the following. The code below searchers for blanks in Column A, highlights them, then it shows a message every time a cell has blanks with the cell location (e.g."No Value, in $A$1"). I copy these cells locations in another tab called "Results".

I need help with the following. I want to somehow for the message to show once with the list of cells that have blanks and their location. I do not want the message to pop out every time it finds a blank cell (I could have hundreds of blank cells in a file and clicking Ok for each one is not practical). Then that list of values will be copied in my "Results" sheet.

Here is the code I currently have:

Sub CeldasinInfo()

Dim i As Long, r As Range, coltoSearch As String

coltoSearch = "A"

For i = 1 To Range(coltoSearch & Rows.Count).End(xlUp).Row
    Set r = Range(coltoSearch & i)
    If Len(r.Value) = 0 Then
         r.Interior.ColorIndex = 3 ' Red
          r.Select
          MsgBox "No Value, in " & r.Address
          Sheets("Results").Range("A" & Sheets("Results").Range("A" &   Rows.Count).End(xlUp).Row).Offset(1, 0).Formula = r.Address
    End If
Next
End Sub

Thank so much in advance. I would really appreciate your help with this.

Upvotes: 1

Views: 3129

Answers (2)

brettdj
brettdj

Reputation: 55702

If your cells are truly blank you can avoid a range loop and use SpecialCells

Sub CeldasinInfo()

Dim rng1 As Range
Dim coltoSearch As String

coltoSearch = "A"

On Error Resume Next
Set rng1 = Columns(coltoSearch).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng1 Is Nothing Then Exit Sub

rng1.Interior.ColorIndex = 3 ' Red
MsgBox "No Value, in " & rng1.Address

End Sub

Upvotes: 0

FreeMan
FreeMan

Reputation: 5687

Something like this should work for you:

Sub CeldasinInfo()

Dim i As Long, r As Range, coltoSearch As String
Dim Result as String
Dim ErrCount as integer

ErrCount = 0
coltoSearch = "A"
coltoSearch = Range("1:1").find(What:="Hours", LookIn:=xlValues, LookAt:=xlWhole).Column

Result = "No Value in:" & vbcrlf
For i = 1 To Range(coltoSearch & Rows.Count).End(xlUp).Row
    Set r = Range(coltoSearch & i)
    If Len(r.Value) = 0 Then
         r.Interior.ColorIndex = 3 ' Red
          r.Select
'          MsgBox "No Value, in " & r.Address
          Result = Result & r.Address & vbcrlf
          ErrCount = ErrCount + 1
          if ErrCount Mod 10 = 0 then    'change to 15 or 20 or whatever works well
            MsgBox Result
            Result = "No Value in:" & vbcrlf
          End If
          Sheets("Results").Range("A" & Sheets("Results").Range("A" &   Rows.Count).End(xlUp).Row).Offset(1, 0).Formula = r.Address
    End If
Next

If ErrCount > 0 then
  MsgBox "There were " & ErrCount & " errors detected." & vbcrlf & result
else
  MsgBox "No errors detected"
End If
End Sub

This will give you each address on a separate line in the MsgBox. If there are hundreds of errors likely, this will result in a very long MsgBox output, and I'm not sure how it will handle that. You may need to add in a counter and display the message every 10, 15 or 20 errors for a better looking output.

Upvotes: 1

Related Questions