| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter formula and fill down
How can I enter a formula into cell E2 and then fill the formula down to the
last row with data on the sheet. For example: enter into E2: =IF(D20,TODAY()-(D2+30)," ") Fill into E3: =IF(D30,TODAY()-(D3+30)," ") ....Fill to last Row on sheet with data in column D. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter formula and fill down
Josh,
Range("E2:E" & Range("D65536").End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-1]0,TODAY()-(RC[-1]+30),"" "")" HTH, Bernie MS Excel MVP "Josh O." wrote in message ... How can I enter a formula into cell E2 and then fill the formula down to the last row with data on the sheet. For example: enter into E2: =IF(D20,TODAY()-(D2+30)," ") Fill into E3: =IF(D30,TODAY()-(D3+30)," ") ...Fill to last Row on sheet with data in column D. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter formula and fill down
Thanks Bernie.
How about the same thing with a different formula put into column "o"? =if(g20,sum(h2:n2)," ") "Bernie Deitrick" wrote: Josh, Range("E2:E" & Range("D65536").End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-1]0,TODAY()-(RC[-1]+30),"" "")" HTH, Bernie MS Excel MVP "Josh O." wrote in message ... How can I enter a formula into cell E2 and then fill the formula down to the last row with data on the sheet. For example: enter into E2: =IF(D20,TODAY()-(D2+30)," ") Fill into E3: =IF(D30,TODAY()-(D3+30)," ") ...Fill to last Row on sheet with data in column D. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter formula and fill down
Josh,
Range("O2:O" & Range("G65536").End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-8]0,SUM(RC[-7]:RC[-1]),"" "")" This will extend down column O to match the values in column G. HTH, Bernie MS Excel MVP "Josh O." wrote in message ... Thanks Bernie. How about the same thing with a different formula put into column "o"? =if(g20,sum(h2:n2)," ") "Bernie Deitrick" wrote: Josh, Range("E2:E" & Range("D65536").End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-1]0,TODAY()-(RC[-1]+30),"" "")" HTH, Bernie MS Excel MVP "Josh O." wrote in message ... How can I enter a formula into cell E2 and then fill the formula down to the last row with data on the sheet. For example: enter into E2: =IF(D20,TODAY()-(D2+30)," ") Fill into E3: =IF(D30,TODAY()-(D3+30)," ") ...Fill to last Row on sheet with data in column D. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter formula and fill down
Thanks Bernie. I had tried a similiar code, but it keep hanging up at the
formula. Works perfect! "Bernie Deitrick" wrote: Josh, Range("O2:O" & Range("G65536").End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-8]0,SUM(RC[-7]:RC[-1]),"" "")" This will extend down column O to match the values in column G. HTH, Bernie MS Excel MVP "Josh O." wrote in message ... Thanks Bernie. How about the same thing with a different formula put into column "o"? =if(g20,sum(h2:n2)," ") "Bernie Deitrick" wrote: Josh, Range("E2:E" & Range("D65536").End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-1]0,TODAY()-(RC[-1]+30),"" "")" HTH, Bernie MS Excel MVP "Josh O." wrote in message ... How can I enter a formula into cell E2 and then fill the formula down to the last row with data on the sheet. For example: enter into E2: =IF(D20,TODAY()-(D2+30)," ") Fill into E3: =IF(D30,TODAY()-(D3+30)," ") ...Fill to last Row on sheet with data in column D. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter formula and fill down
Josh,
The easiest way to get the formula correct is to enter the formula into a cell, and get it working. Then select the cell turn on the macro recorder, press F2, press Enter, then turn off the recorder. You will then get the correct code-to-formula syntax. HTH, Bernie MS Excel MVP Thanks Bernie. I had tried a similiar code, but it keep hanging up at the formula. Works perfect! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter formula and fill down
This post is absolutely awesome, I was having no luck finding a solution to
this problem! Out of curiosity, what is the significance of the 65536? "Bernie Deitrick" wrote: Range("E2:E" & Range("D65536").End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-1]0,TODAY()-(RC[-1]+30),"" "")" |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter formula and fill down
Out of curiosity, what is the significance of the 65536?
That is the last row in the worksheet, = 2^16. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "J Cope At Stratis" wrote in message ... This post is absolutely awesome, I was having no luck finding a solution to this problem! Out of curiosity, what is the significance of the 65536? "Bernie Deitrick" wrote: Range("E2:E" & Range("D65536").End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-1]0,TODAY()-(RC[-1]+30),"" "")" |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter formula and fill down
Bernie -
This thread has been great for me - I always had such a hard time transferring the code to the RC syntax. However, I am having a problem with a bit of code and I was wondering if you could help me find my mistake? Range("D2:D" & Range("G65536").End(xlUp).Row).FormulaR1C1 = "=CONCATENATE(LEFT(RC[-2],3),"" "",RC[-1])" What I am trying to do is identify the filldown cells and then enter in the concatenate formula I have here. It is only working for the top cell and then stopping. Am I not identifying the range correctly? Thank you in advance for any help you can provide! "Bernie Deitrick" wrote: Josh, The easiest way to get the formula correct is to enter the formula into a cell, and get it working. Then select the cell turn on the macro recorder, press F2, press Enter, then turn off the recorder. You will then get the correct code-to-formula syntax. HTH, Bernie MS Excel MVP Thanks Bernie. I had tried a similiar code, but it keep hanging up at the formula. Works perfect! |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter formula and fill down
Your code looks at column G to find the last row that should get the
formular1c1. If there's not enough data in column G, then maybe you should use a different column??? Katie wrote: Bernie - This thread has been great for me - I always had such a hard time transferring the code to the RC syntax. However, I am having a problem with a bit of code and I was wondering if you could help me find my mistake? Range("D2:D" & Range("G65536").End(xlUp).Row).FormulaR1C1 = "=CONCATENATE(LEFT(RC[-2],3),"" "",RC[-1])" What I am trying to do is identify the filldown cells and then enter in the concatenate formula I have here. It is only working for the top cell and then stopping. Am I not identifying the range correctly? Thank you in advance for any help you can provide! "Bernie Deitrick" wrote: Josh, The easiest way to get the formula correct is to enter the formula into a cell, and get it working. Then select the cell turn on the macro recorder, press F2, press Enter, then turn off the recorder. You will then get the correct code-to-formula syntax. HTH, Bernie MS Excel MVP Thanks Bernie. I had tried a similiar code, but it keep hanging up at the formula. Works perfect! -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter formula and fill down
Dave -
Thank you, I'm an idiot. :-) I changed all the references and never changed where it looked. Thank you. :-) "Dave Peterson" wrote: Your code looks at column G to find the last row that should get the formular1c1. If there's not enough data in column G, then maybe you should use a different column??? Katie wrote: Bernie - This thread has been great for me - I always had such a hard time transferring the code to the RC syntax. However, I am having a problem with a bit of code and I was wondering if you could help me find my mistake? Range("D2:D" & Range("G65536").End(xlUp).Row).FormulaR1C1 = "=CONCATENATE(LEFT(RC[-2],3),"" "",RC[-1])" What I am trying to do is identify the filldown cells and then enter in the concatenate formula I have here. It is only working for the top cell and then stopping. Am I not identifying the range correctly? Thank you in advance for any help you can provide! "Bernie Deitrick" wrote: Josh, The easiest way to get the formula correct is to enter the formula into a cell, and get it working. Then select the cell turn on the macro recorder, press F2, press Enter, then turn off the recorder. You will then get the correct code-to-formula syntax. HTH, Bernie MS Excel MVP Thanks Bernie. I had tried a similiar code, but it keep hanging up at the formula. Works perfect! -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter formula and fill down
Welcome to the club!!!
Katie wrote: Dave - Thank you, I'm an idiot. :-) I changed all the references and never changed where it looked. Thank you. :-) "Dave Peterson" wrote: Your code looks at column G to find the last row that should get the formular1c1. If there's not enough data in column G, then maybe you should use a different column??? Katie wrote: Bernie - This thread has been great for me - I always had such a hard time transferring the code to the RC syntax. However, I am having a problem with a bit of code and I was wondering if you could help me find my mistake? Range("D2:D" & Range("G65536").End(xlUp).Row).FormulaR1C1 = "=CONCATENATE(LEFT(RC[-2],3),"" "",RC[-1])" What I am trying to do is identify the filldown cells and then enter in the concatenate formula I have here. It is only working for the top cell and then stopping. Am I not identifying the range correctly? Thank you in advance for any help you can provide! "Bernie Deitrick" wrote: Josh, The easiest way to get the formula correct is to enter the formula into a cell, and get it working. Then select the cell turn on the macro recorder, press F2, press Enter, then turn off the recorder. You will then get the correct code-to-formula syntax. HTH, Bernie MS Excel MVP Thanks Bernie. I had tried a similiar code, but it keep hanging up at the formula. Works perfect! -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| My worksheet automatically adds a fill color when I enter text. | Excel Discussion (Misc queries) | |||
| Enter a value in wks1-Find that value on wks2-Auto fill cells in w | Excel Worksheet Functions | |||
| i want cells to auto fill with colour when i enter a number | Excel Discussion (Misc queries) | |||
| What does hitting Ctrl + Shift + Enter to enter a formula do??? Help a n00b out. | Excel Worksheet Functions | |||
| Enter last name, auto fill full name & address--HOW? | Excel Discussion (Misc queries) | |||
Linear Mode
