I adjusted some other code I had incorrect and the macros works fine now.

Thanks for your help with the "PRJ" type code that worked just fine

Thanks again for your help!


"Rick Rothstein" wrote:

As for your second question, if I understood it correctly, try this code
(which also implements my previous response to your first question)...

For Each Cell2 In Selection
If Cell2.Value = "#N/A" Then
If Cell2.Offset(0, -1).Value = "General Admin - LA" Then
Cell2.Value = "0"
Cell2.Offset(0, 1).Value = "AD"
End If
Else
If Cell2.Offset(0, -1).Value = "Catalog - Help Desk Support" Then
Cell2.Value = "PRJ00000513"
Cell2.Offset(0, 1).Value = "HD"
ElseIf Left(Cell2.Offset(0, -1).Value, 3) = "PRJ" Then
Cell2.Value = "PRJ00000" & Mid(Cell2.Offset(0, -1).Value, 4, 3)
End If
End If
Next Cell2

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
The Value in your cell is "Error 2042"; however, the Text is "#N/A". So,
you should be able to do this...

If Cell2.Text = "#N/A" Then

--
Rick (MVP - Excel)


"Bud" wrote in message
...
Hello

I have a worksheet titled Fill-Down. It populates data from another
worksheet matching on descroptions from yet another worksheet.
If the matching can not find a match there is a #N/A that is placed in
the
column E field.

I want to go down this list(Column E) looking for the #N/A. If I find
this I
want to check on the description of that row which is in the column to
the
left. If that description matches what I have hard coded I want to
populate
over that #N/A in columns E and fill in another set value in column F in
the
same row but 1 column to the right.

I set some code in but it doesn't work. It stops right at If
Cell2.Value =
"#N/A" Then
1. Can someone look at this and suggest a fix?

2. One other question.....Cell2.Offset(0, -1).Value is the description
column field as I can see the description when I test this. Beyond the
formula below I want another ELSE IF to recognize if PRJ exists in the
1st 3
characters of that offset and than if it does write out PRJ00000 and
append
the 4th,5th,6th characters from cell2.offset(0,-1) into the current
active
cell Cell2.Offset(0, 0).Value
How do I do this?

Sheets("Fill-Down").Select
Cells.Select
Columns("e:e").Select
Dim Cell2 As Range

For Each Cell2 In Selection
If Cell2.Value = "#N/A" Then
If Cell2.Offset(0, -1).Value = "General Admin - LA" Then
Cell2.Offset(0, 0).Value = "0"
Cell2.Offset(0, 1).Value = "AD"
End If
Else
If Cell2.Offset(0, -1).Value = "Catalog - Help Desk Support"
Then
Cell2.Offset(0, 0).Value = "PRJ00000513"
Cell2.Offset(0, 1).Value = "HD"
End If
End If
Next Cell2