Help! Search Range Using Two Criterias & Returning Cell Contents.

[restitus]

Can anyone help?

Problem! I have a table (called "callback") which I can search and
return the cell value using "Cell.Offset". The code listed below
works well. However, I repeat the code four times to get four
different cell values in the same row, based on a single criteria.

Q. 1. Is there away to write this code once and return all four
values that I'm looking for using Cell.Offset?

Q. 2. More importantly, is there away to do the same search using
"two criterias" and returning four cell values? The values are all
located in the row in which the criterias are found.

Bascially, what I'm after is locating a employee based on his/her
employee number and work area. Using these two criteria, once found,
copy their employee particulars to a named range on the worksheet.

Sample Code:

Range("UC").Select
On Error Resume Next
For Each Cell In Range("CallBackList")
If Cell.Value = "PC" Then
Cell.Offset(0, 0).Copy
ActiveCell.PasteSpecial xlPasteValues
ActiveCell.Offset(1, 0).Select
End If
Next Cell

The above code allows me to locate and copy one cell value to my
worksheet.

Thanks for your assistance in advance,
Eugene

 

[Sebation.G]

guess the two criterias in the same row
Sub test()
On Error Resume Next

Range("A1:f17").Name = "CallBackList"
i = 0
For Each CELL In Range("CallBackList")
If CELL.Value = "employnumber" And CELL.Offset(, 1) = "workarea" Then
'u can change them with your criterias address
i = i + 1
CELL.Offset(, 2).Copy Range("g" & i) 'u can change the destination u
want to copy to
End If
Next CELL
End Sub

HTH