Macro to give me a list of employee who do not have a rate in a 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,

Ok so heres what i need,

I have a sheet with employees names and there daily hours,

but the hourly rate is looked up from the employees tab,
if that employee is not listed I get a zero as there rate,

I want a macro that when i run it it check if any of the rows have a zero in the hourly rate column and if so give me that name in a pop up box,

So here's the fully details
last row can be found in column A,

Look down Column N and see if any cells in N2 to lastrow area zero "0"
if so, take the first and last names from column C and D

Now there will be lots of duplicate names, so you can get rid of any duplicate names in column D (D is set up to always be unique for each employee so just D id fine)

so we should now have a list of all the employees who do not have a rate,

give me a popup bow with "these employees don't have a rate"

then list then as Data in column C then column D

A bit like this

these employees don't have a rate
Tony Wats
Bob Holmes
Etc so this is column D
Column C

please help if you can,

Thanks

Tony
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Try this.

If you want the output "D" and "C", change the letters on this line:
sName = Range("C" & c.Row).Value & " " & Range("D" & c.Row).Value

VBA Code:
Sub these_employees_dont_have_rate()
  Dim dic As Object
  Dim c As Range
  Dim sName As String, cad As String
 
  Set dic = CreateObject("Scripting.Dictionary")
 
  For Each c In Range("N2:N" & Range("A" & Rows.Count).End(3).Row)
    If c.Value = 0 Then
      sName = Range("C" & c.Row).Value & " " & Range("D" & c.Row).Value
      If Not dic.exists(sName) Then
        dic(sName) = Empty
        cad = cad & sName & vbCr
      End If
    End If
  Next
  If dic.Count > 0 Then
    MsgBox "These employees don't have a rate" & vbCr & cad
  Else
    MsgBox "There are no employees without rate"
  End If
End Sub
 
Upvote 0
Hi dante,
I'm just getting a debug "Type mismatch" for If c.Value = 0 Then
 
Upvote 0
Look down Column N and see if any cells in N2 to lastrow area zero "0"

I guess in column N you must have numeric values equal to 0 or different from 0, but numeric values.
Do you have texts?
Do you have formulas?
Do you have formulas that return error like #N/A, #VALUE, etc?

Try the following, but keep in mind that we can't know all the possible values you can have in a cell.
It would help next time if you tell us.

VBA Code:
Sub these_employees_dont_have_rate()
  Dim dic As Object
  Dim c As Range
  Dim sName As String, cad As String
  
  Set dic = CreateObject("Scripting.Dictionary")
  
  For Each c In Range("N2:N" & Range("A" & Rows.Count).End(3).Row)
    If Not IsError(c.Value) Then
      If IsNumeric(c.Value) Then
        If c.Value = 0 Then
          sName = Range("C" & c.Row).Value & " " & Range("D" & c.Row).Value
          If Not dic.exists(sName) Then
            dic(sName) = Empty
            cad = cad & sName & vbCr
          End If
        End If
      End If
    End If
  Next
  If dic.Count > 0 Then
    MsgBox "These employees don't have a rate" & vbCr & cad
  Else
    MsgBox "There are no employees without rate"
  End If
End Sub
 
Upvote 0
I played about with it and managed to get it working,
Its exactly what i needed now,
Thank you so much for your code,
Thanks
Tony
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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