Create message box telling me the missing name in column

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,194
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi everyone,

really need your help,
i have a sheet called "Apiler"

in column D are a list of names and in column N a list of values, if data is missing column N gives me a "#N/A"

I want a macro that can let me know these are there

so when i run the macro, i want it to Look down column N if it finds a "#N/A" in any of the cell values it brings up a mesage box saying "Please add a value for (and the name that is in coulmn D of the row with the error)

so if it was row 15 and D15 say dave it would messagebox me "Please add a value for Dave"

please help if you can

thanks

Tony
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi Tony,

Try this:

VBA Code:
Option Explicit
Sub Macro1()

    Dim lngMyRow As Long
    Dim lngLastRow As Long
    
    Application.ScreenUpdating = False
    
    With Sheets("Apiler")
        lngLastRow = .Cells(Rows.Count, "N").End(xlUp).Row
        For lngMyRow = 2 To lngLastRow 'Starts from Row 2. Change to suit.
            If IsError(.Range("N" & lngMyRow)) = True Then
                MsgBox "Please add a value for " & .Range("D" & lngMyRow).Value
            End If
        Next lngMyRow
    End With
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert
 
Upvote 0
Another option, if column N has formulae
VBA Code:
Sub tonywatson()
   Dim Rng As Range, Cl As Range
   Dim Msg As String
   
   With Sheets("Apiler").Range("N:N")
      On Error Resume Next
      Set Rng = .SpecialCells(xlFormulas, xlErrors)
      On Error GoTo 0
   End With
   If Rng Is Nothing Then Exit Sub
   For Each Cl In Rng
      Msg = Msg & vbLf & Cl.Offset(, -10).Value
   Next Cl
   MsgBox "Please add a value for" & vbLf & Msg
End Sub
 
Upvote 0
This will cater for any #N/A values
VBA Code:
Sub tonywatson()
   Dim x As Variant
   x = Filter([transpose(if(isna(Apiler!N1:N1000),Apiler!D1:D1000,false))], False, False)
   If UBound(x) >= 0 Then MsgBox "please enter data for" & vbLf & Join(x, vbLf)
End Sub
 
Upvote 0
Assuming the cells in Column N contain formulas, here is another macro that you can consider...
VBA Code:
Sub Macro2()
  Dim Cell As Range
  On Error GoTo Whoops
  For Each Cell In Sheets("Apiler").Columns("N").SpecialCells(xlFormulas, xlErrors)
    MsgBox "Please add a value for " & Cell.Offset(, -10).Value
  Next
Whoops:
End Sub
 
Last edited:
Upvote 0
Thank you everyone,
wow spoilt for choice :)
big thank you to all of you for your help,
this has solved the problem.

Thank you to Fluff, Rick and Robert
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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