Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating column N with a formula if column A is Null or Blank
Yesterday, I posted a message on here and received a
number of good responses from Bernie Deitrick and Tom Ogilvy (Thank you to you both). However, due to the fact that I probably didn't state what my problem was too clearly (as i was trying to keep it simple) I still have not yet achieved my ultimate result. So today, I am going to try and be a little clearer to see if I can achieve what I am setting out to do. My spreadhsheet contains a number of columns. Column A normally has an id in it. Where it does, column N is populated with a value. This value is the difference between two other columns (column G minus column C), although this field is already populated before the data gets into Excel (it is calculated in Access). On a number of rows, column A is blank/null, however, there is data in the other columns on the corresponding row. What i want to do is write a formula in column N that calculates the difference between column G and column C WHERE column a is Null/Blank. Where column A is not blank/null, I do not want to overwrite the value that is already in the cell. Thanks in advance Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating column N with a formula if column A is Null or Blank
hi,
=if(a1="",g1-c1,a1) if a1 ISNULL then subtrace c from g exlse value of a. enter this in N. -----Original Message----- Yesterday, I posted a message on here and received a number of good responses from Bernie Deitrick and Tom Ogilvy (Thank you to you both). However, due to the fact that I probably didn't state what my problem was too clearly (as i was trying to keep it simple) I still have not yet achieved my ultimate result. So today, I am going to try and be a little clearer to see if I can achieve what I am setting out to do. My spreadhsheet contains a number of columns. Column A normally has an id in it. Where it does, column N is populated with a value. This value is the difference between two other columns (column G minus column C), although this field is already populated before the data gets into Excel (it is calculated in Access). On a number of rows, column A is blank/null, however, there is data in the other columns on the corresponding row. What i want to do is write a formula in column N that calculates the difference between column G and column C WHERE column a is Null/Blank. Where column A is not blank/null, I do not want to overwrite the value that is already in the cell. Thanks in advance Steve . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating column N with a formula if column A is Null or Blank
Yesterday it was C, D, and E and addition, so yes, that wouldn't have
described this situation. Dim rng as Range, sForm as String Dim rng1 as Range Dim sStr as String On Error Resume Next set rng1 = Intersect(Activesheet.UsedRange, _ Columns(1)).SpecialCells(xlBlanks) On Error goto 0 if rng1 is nothing then msgbox "No blank cells in column A" exit sub End if set rng = Intersect(rng1.EntireRow, _ Columns(14)) sStr = rng(1).row sForm = "=G" & sStr _ & "-C" & sStr rng.Formula = sForm is adjusted to do what you describe. if you get the response that there are no blank cells in Column A, then it is possible that the cells look blank but are not. Then try this code: Dim sForm as String Dim rng1 as Range, cell as Range Dim sStr as String set rng1 = InterSect(Activesheet.UsedRange, _ Columns(14)).Cells set rng1 = rng1.offset(0,-13) for each cell in rng1 if len(trim(cell.value)) = 0 then sStr = cell.row sForm = "=G" & sStr _ & "-C" & sStr cell.offset(0,13).Formula = sForm end if Next -- Regards, Tom Ogilvy "Steve" wrote in message ... Yesterday, I posted a message on here and received a number of good responses from Bernie Deitrick and Tom Ogilvy (Thank you to you both). However, due to the fact that I probably didn't state what my problem was too clearly (as i was trying to keep it simple) I still have not yet achieved my ultimate result. So today, I am going to try and be a little clearer to see if I can achieve what I am setting out to do. My spreadhsheet contains a number of columns. Column A normally has an id in it. Where it does, column N is populated with a value. This value is the difference between two other columns (column G minus column C), although this field is already populated before the data gets into Excel (it is calculated in Access). On a number of rows, column A is blank/null, however, there is data in the other columns on the corresponding row. What i want to do is write a formula in column N that calculates the difference between column G and column C WHERE column a is Null/Blank. Where column A is not blank/null, I do not want to overwrite the value that is already in the cell. Thanks in advance Steve |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating column N with a formula if column A is Null or Blank
Tom
Many thanks for that. It did say that there were no blank cells the first time so i used the second bit of code and it worked fine. For my information, why did I get that result with the first bit of code ? Finally, I now want to append this code to look at column H in adiition to A. (i.e.if column A or H are blank .... Any ideas? Thanks once again Steve -----Original Message----- Yesterday it was C, D, and E and addition, so yes, that wouldn't have described this situation. Dim rng as Range, sForm as String Dim rng1 as Range Dim sStr as String On Error Resume Next set rng1 = Intersect(Activesheet.UsedRange, _ Columns(1)).SpecialCells(xlBlanks) On Error goto 0 if rng1 is nothing then msgbox "No blank cells in column A" exit sub End if set rng = Intersect(rng1.EntireRow, _ Columns(14)) sStr = rng(1).row sForm = "=G" & sStr _ & "-C" & sStr rng.Formula = sForm is adjusted to do what you describe. if you get the response that there are no blank cells in Column A, then it is possible that the cells look blank but are not. Then try this code: Dim sForm as String Dim rng1 as Range, cell as Range Dim sStr as String set rng1 = InterSect(Activesheet.UsedRange, _ Columns(14)).Cells set rng1 = rng1.offset(0,-13) for each cell in rng1 if len(trim(cell.value)) = 0 then sStr = cell.row sForm = "=G" & sStr _ & "-C" & sStr cell.offset(0,13).Formula = sForm end if Next -- Regards, Tom Ogilvy "Steve" wrote in message ... Yesterday, I posted a message on here and received a number of good responses from Bernie Deitrick and Tom Ogilvy (Thank you to you both). However, due to the fact that I probably didn't state what my problem was too clearly (as i was trying to keep it simple) I still have not yet achieved my ultimate result. So today, I am going to try and be a little clearer to see if I can achieve what I am setting out to do. My spreadhsheet contains a number of columns. Column A normally has an id in it. Where it does, column N is populated with a value. This value is the difference between two other columns (column G minus column C), although this field is already populated before the data gets into Excel (it is calculated in Access). On a number of rows, column A is blank/null, however, there is data in the other columns on the corresponding row. What i want to do is write a formula in column N that calculates the difference between column G and column C WHERE column a is Null/Blank. Where column A is not blank/null, I do not want to overwrite the value that is already in the cell. Thanks in advance Steve . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating column N with a formula if column A is Null or Blank
Dim sForm as String Dim rng1 as Range, cell as Range Dim sStr as String set rng1 = InterSect(Activesheet.UsedRange, _ Columns(14)).Cells set rng1 = rng1.offset(0,-13) for each cell in rng1 ' adjusted to check A and H if len(trim(cell.value)) = 0 or _ len(trim(cell.offset(0,7)) = 0 then sStr = cell.row sForm = "=G" & sStr _ & "-C" & sStr cell.offset(0,13).Formula = sForm end if Next It is possible there is a null string in the cells or something similar, since the data is imported. -- Regards, Tom Ogilvy "Steve" wrote in message ... Tom Many thanks for that. It did say that there were no blank cells the first time so i used the second bit of code and it worked fine. For my information, why did I get that result with the first bit of code ? Finally, I now want to append this code to look at column H in adiition to A. (i.e.if column A or H are blank .... Any ideas? Thanks once again Steve -----Original Message----- Yesterday it was C, D, and E and addition, so yes, that wouldn't have described this situation. Dim rng as Range, sForm as String Dim rng1 as Range Dim sStr as String On Error Resume Next set rng1 = Intersect(Activesheet.UsedRange, _ Columns(1)).SpecialCells(xlBlanks) On Error goto 0 if rng1 is nothing then msgbox "No blank cells in column A" exit sub End if set rng = Intersect(rng1.EntireRow, _ Columns(14)) sStr = rng(1).row sForm = "=G" & sStr _ & "-C" & sStr rng.Formula = sForm is adjusted to do what you describe. if you get the response that there are no blank cells in Column A, then it is possible that the cells look blank but are not. Then try this code: Dim sForm as String Dim rng1 as Range, cell as Range Dim sStr as String set rng1 = InterSect(Activesheet.UsedRange, _ Columns(14)).Cells set rng1 = rng1.offset(0,-13) for each cell in rng1 if len(trim(cell.value)) = 0 then sStr = cell.row sForm = "=G" & sStr _ & "-C" & sStr cell.offset(0,13).Formula = sForm end if Next -- Regards, Tom Ogilvy "Steve" wrote in message ... Yesterday, I posted a message on here and received a number of good responses from Bernie Deitrick and Tom Ogilvy (Thank you to you both). However, due to the fact that I probably didn't state what my problem was too clearly (as i was trying to keep it simple) I still have not yet achieved my ultimate result. So today, I am going to try and be a little clearer to see if I can achieve what I am setting out to do. My spreadhsheet contains a number of columns. Column A normally has an id in it. Where it does, column N is populated with a value. This value is the difference between two other columns (column G minus column C), although this field is already populated before the data gets into Excel (it is calculated in Access). On a number of rows, column A is blank/null, however, there is data in the other columns on the corresponding row. What i want to do is write a formula in column N that calculates the difference between column G and column C WHERE column a is Null/Blank. Where column A is not blank/null, I do not want to overwrite the value that is already in the cell. Thanks in advance Steve . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with Formula. Need it to return Null, not Blank | Excel Discussion (Misc queries) | |||
insert a blank column between each column in a data file | Excel Discussion (Misc queries) | |||
How to do calculations with Null in the column? | Excel Discussion (Misc queries) | |||
Warning message if one column contains any text and another column is blank | Excel Worksheet Functions | |||
How do I Excel countif column a =? and column b = null ? | Excel Worksheet Functions |