Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Help
I was wondering if anyone could point me in the right direction on a few
questions I have. I would like to evaluate the value in a cell in a column and if it is within a certain range write a value to a new column. I would like to do this for and entire column? Meaning write the function once and have it work for the whole column. Also is it possible to have a formula change the value of a cell and write the results back to the same cell. i.e n1 has a value of 25. the value I really need is 25/2. This is on imported data. I prefer not to write to another column. Basically as the data is imported or inputted the calc takes place. Thanks in advance for the help. Regards, Tim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Help
On Thu, 30 Aug 2007 09:22:09 -0400, Tim Di Prinzio wrote:
I was wondering if anyone could point me in the right direction on a few questions I have. I would like to evaluate the value in a cell in a column and if it is within a certain range write a value to a new column. I would like to do this for and entire column? Meaning write the function once and have it work for the whole column. Place your formula in the new column; then fill down the required number of rows. Also is it possible to have a formula change the value of a cell and write the results back to the same cell. i.e n1 has a value of 25. the value I really need is 25/2. This is on imported data. I prefer not to write to another column. Basically as the data is imported or inputted the calc takes place. It is not possible. Functions return values, they do not affect other cells. There are other ways to do what you require, however. You could, for example, place a number "2" in some blank cell. Edit/Copy that cell. Then select the cells which need to be modified and Edit/Paste Special/Divide. This can also be done using a VBA macro. Thanks in advance for the help. Regards, Tim --ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Help
Tim,
Get a formula to work in the column where you want the new values to appear, something along the lines of =IF(AND(A23,A2<5),"New Value","") and then when you have it working, record a macro where you select that cell, press F2 to enter Edit mode, and then press Enter to enter the formula. Your code will look like this, for the formula above entered into cell C2: Range("C2").Select ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]3,RC[-2]<5),""New Value"","""")" Range("C3").Select Range("C3").Select Then change the code to include the entire C column: Range("C2:C" & Cells(Rows.Count, 2).End(xlUp).Row).FormulaR1C1 = _ "=IF(AND(RC[-2]3,RC[-2]<5),""New Value"","""")" This code will match column B's entries.... HTH, Bernie MS Excel MVP "Tim Di Prinzio" wrote in message ... I was wondering if anyone could point me in the right direction on a few questions I have. I would like to evaluate the value in a cell in a column and if it is within a certain range write a value to a new column. I would like to do this for and entire column? Meaning write the function once and have it work for the whole column. Also is it possible to have a formula change the value of a cell and write the results back to the same cell. i.e n1 has a value of 25. the value I really need is 25/2. This is on imported data. I prefer not to write to another column. Basically as the data is imported or inputted the calc takes place. Thanks in advance for the help. Regards, Tim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Help
Bernie Deitrick wrote:
Tim, Get a formula to work in the column where you want the new values to appear, something along the lines of =IF(AND(A23,A2<5),"New Value","") and then when you have it working, record a macro where you select that cell, press F2 to enter Edit mode, and then press Enter to enter the formula. Your code will look like this, for the formula above entered into cell C2: Range("C2").Select ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]3,RC[-2]<5),""New Value"","""")" Range("C3").Select Range("C3").Select Then change the code to include the entire C column: Range("C2:C" & Cells(Rows.Count, 2).End(xlUp).Row).FormulaR1C1 = _ "=IF(AND(RC[-2]3,RC[-2]<5),""New Value"","""")" This code will match column B's entries.... HTH, Bernie MS Excel MVP "Tim Di Prinzio" wrote in message ... I was wondering if anyone could point me in the right direction on a few questions I have. I would like to evaluate the value in a cell in a column and if it is within a certain range write a value to a new column. I would like to do this for and entire column? Meaning write the function once and have it work for the whole column. Also is it possible to have a formula change the value of a cell and write the results back to the same cell. i.e n1 has a value of 25. the value I really need is 25/2. This is on imported data. I prefer not to write to another column. Basically as the data is imported or inputted the calc takes place. Thanks in advance for the help. Regards, Tim Thanks Bernie - But I think I need to do this with vba code as each cell could be a different value. I think a case construct would work. But i am unsure how to apply the vba code to work on the entire colunm... Tim |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Help
Tim,
But I think I need to do this with vba code as each cell could be a different value. I think a case construct would work. But i am unsure how to apply the vba code to work on the entire colunm... Each cell could be a different value - but if you can use one formula, you will still get different results. If each cell has unique upper and lower limits, try using cell references in your formula rather than specific values: =IF(AND(A2B2,A2<C2),"New Value","") etc. HTH, Bernie MS Excel MVP "Tim Di Prinzio" wrote in message ... Bernie Deitrick wrote: Tim, Get a formula to work in the column where you want the new values to appear, something along the lines of =IF(AND(A23,A2<5),"New Value","") and then when you have it working, record a macro where you select that cell, press F2 to enter Edit mode, and then press Enter to enter the formula. Your code will look like this, for the formula above entered into cell C2: Range("C2").Select ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]3,RC[-2]<5),""New Value"","""")" Range("C3").Select Range("C3").Select Then change the code to include the entire C column: Range("C2:C" & Cells(Rows.Count, 2).End(xlUp).Row).FormulaR1C1 = _ "=IF(AND(RC[-2]3,RC[-2]<5),""New Value"","""")" This code will match column B's entries.... HTH, Bernie MS Excel MVP "Tim Di Prinzio" wrote in message ... I was wondering if anyone could point me in the right direction on a few questions I have. I would like to evaluate the value in a cell in a column and if it is within a certain range write a value to a new column. I would like to do this for and entire column? Meaning write the function once and have it work for the whole column. Also is it possible to have a formula change the value of a cell and write the results back to the same cell. i.e n1 has a value of 25. the value I really need is 25/2. This is on imported data. I prefer not to write to another column. Basically as the data is imported or inputted the calc takes place. Thanks in advance for the help. Regards, Tim Thanks Bernie - But I think I need to do this with vba code as each cell could be a different value. I think a case construct would work. But i am unsure how to apply the vba code to work on the entire colunm... Tim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|