Create message box telling me the missing name in column

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,678
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,714
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,821
Office Version
  1. 365
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,821
Office Version
  1. 365
Platform
  1. Windows
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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:

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,678
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,821
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,312
Messages
5,595,430
Members
413,990
Latest member
Kher83

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
Top