| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "if" formula not working- seems to be formatting | Excel Discussion (Misc queries) | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| Replace(ActiveCell.Formula, "Round(","") not working as expected | Excel Programming | |||
| If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming | |||
| Working out age from "Day" "Month" "Year" | Excel Worksheet Functions | |||
Hybrid Mode
