# Macro to give me a list of employee who do not have a rate in a column

#### tonywatsonhelp

##### Well-known Member
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

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
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
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
Hi dante,
I'm just getting a debug "Type mismatch" for If c.Value = 0 Then

#### DanteAmor

##### Well-known Member
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
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
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

Replies
0
Views
190
Replies
3
Views
154
Replies
5
Views
178
Replies
5
Views
133
Replies
0
Views
128

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.

### Which adblocker are you using?

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

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