|
If Cell2.Value = "#N/A" Then formula not working
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 |
If Cell2.Value = "#N/A" Then formula not working
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 |
If Cell2.Value = "#N/A" Then formula not working
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 "Bud" wrote: 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 |
If Cell2.Value = "#N/A" Then formula not working
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 |
If Cell2.Value = "#N/A" Then formula not working
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 "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 |
If Cell2.Value = "#N/A" Then formula not working
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 "Mike H" wrote: 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 "Bud" wrote: 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 |
If Cell2.Value = "#N/A" Then formula not working
Yes, this worked just fine. I adjusted some other code I had incorrect and
the macros works fine now. Thanks again for your help! "Mike H" wrote: 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 "Bud" wrote: 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 |
If Cell2.Value = "#N/A" Then formula not working
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 |
| All times are GMT +1. The time now is 12:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com