If Cell2.Value = "#N/A" Then formula not working

[Bud]

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

 

[Rick Rothstein]

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

 

[Mike H]

This line should solve the #Na issue

If WorksheetFunction.IsNA(Cell2.Value) Then

Q2 If i've understood correctly then try this

If Left(Cell2.Offset(0, -1), 3).Value = "PRJ" Then
Cell2.Offset(0, -1).Value = "PRJ00000" & Mid(Cell2.Offset(0, -1), 4, 3)
End If
Mike

 

[Rick Rothstein]

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

 

[Bud]

The following code runs now using the below instructions but it only changes
the data it is finding using the 1st IF General Admin - LA. These are
scattered to so it must be running thru....

What needs to change?

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

For Each Cell2 In Selection
If WorksheetFunction.IsNA(Cell2.Value) Then
'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

 

[Bud]

The folloowing code runs now using the below instructions but it only changes
the data it is finding using the 1st IF General Admin - LA. These are
scattered to so it must be running thru....

What needs to change?

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

For Each Cell2 In Selection
If WorksheetFunction.IsNA(Cell2.Value) Then
'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

 

[Bud]

Yes, this worked just fine. I adjusted some other code I had incorrect and
the macros works fine now.

Thanks again for your help!

 

[Bud]

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!