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.

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.

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.

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.

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.

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!

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),"" "")"

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),"" "")"

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!

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

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

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