Danny_Kemp
New Member
- Joined
- Jun 19, 2015
- Messages
- 30
Hi all,
I have wrote some code that allows me to search my excel database and return all the employees that report to a pre-defined manager, and puts the values in a list.
I would like to add some further criteria to my search and I am struggling to write the code correctly.
Here is the code:
Sub Find()
Dim Manager
Dim RowCount
Dim Employee
Manager = Sheets("Production Master 20.4.15").Range("E14")
RowCount = Sheets("Kronos").Range("AA" & Rows.Count).End(xlUp).Row
With Worksheets("Kronos").Range("AA4:AA" & RowCount & "")
Set c = .Find(Manager, LookIn:=xlValues, SearchOrder:=xlByRows)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
Sheets("Kronos").Activate
c.Offset(0, -8).Select
Selection.Copy
Sheets("Production Master 20.4.15").Select
Employee = Sheets("Production Master 20.4.15").Range("E" & Rows.Count).End(xlUp).Row + 1
Range("E" & Employee & "").PasteSpecial Paste:=xlPasteValues
Sheets("Kronos").Select
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End Sub
What I want to add is something like this:
If Not c Is Nothing AND c.Offset(0, 5) = "28M" And c.Offset(0, 6) = "MOULD" Then
The idea is I then have 3 search criteria's: Manager, if the cell 5 to the right is 28M and 6 to the right is MOULD. If all of these are correct then return the value in the cell 8 to the left.
Does anyone whole how to add these additional criteria into my code?
I have wrote some code that allows me to search my excel database and return all the employees that report to a pre-defined manager, and puts the values in a list.
I would like to add some further criteria to my search and I am struggling to write the code correctly.
Here is the code:
Sub Find()
Dim Manager
Dim RowCount
Dim Employee
Manager = Sheets("Production Master 20.4.15").Range("E14")
RowCount = Sheets("Kronos").Range("AA" & Rows.Count).End(xlUp).Row
With Worksheets("Kronos").Range("AA4:AA" & RowCount & "")
Set c = .Find(Manager, LookIn:=xlValues, SearchOrder:=xlByRows)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
Sheets("Kronos").Activate
c.Offset(0, -8).Select
Selection.Copy
Sheets("Production Master 20.4.15").Select
Employee = Sheets("Production Master 20.4.15").Range("E" & Rows.Count).End(xlUp).Row + 1
Range("E" & Employee & "").PasteSpecial Paste:=xlPasteValues
Sheets("Kronos").Select
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End Sub
What I want to add is something like this:
If Not c Is Nothing AND c.Offset(0, 5) = "28M" And c.Offset(0, 6) = "MOULD" Then
The idea is I then have 3 search criteria's: Manager, if the cell 5 to the right is 28M and 6 to the right is MOULD. If all of these are correct then return the value in the cell 8 to the left.
Does anyone whole how to add these additional criteria into my code?