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
2,929
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,916
Office Version
  1. 2007
Platform
  1. Windows
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
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,929
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi dante,
I'm just getting a debug "Type mismatch" for If c.Value = 0 Then
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,916
Office Version
  1. 2007
Platform
  1. Windows
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
 

tonywatsonhelp

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,916
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,147,635
Messages
5,742,245
Members
423,717
Latest member
rubthenut

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