Enter formula and fill down

[Guest]

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(D2>0,TODAY()-(D2+30)," ")
Fill into E3: =IF(D3>0,TODAY()-(D3+30)," ")
....Fill to last Row on sheet with data in column D.

 

[Bernie Deitrick]

Josh,

Range("E2:E" & Range("D65536").End(xlUp).Row).FormulaR1C1 = _
"=IF(RC[-1]>0,TODAY()-(RC[-1]+30),"" "")"

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks Bernie.

How about the same thing with a different formula put into column "o"?
=if(g2>0,sum(h2:n2)," ")

Josh,

Range("E2:E" & Range("D65536").End(xlUp).Row).FormulaR1C1 = _
"=IF(RC[-1]>0,TODAY()-(RC[-1]+30),"" "")"

HTH,
Bernie
MS Excel MVP

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(D2>0,TODAY()-(D2+30)," ")
Fill into E3: =IF(D3>0,TODAY()-(D3+30)," ")
...Fill to last Row on sheet with data in column D.

 

[Bernie Deitrick]

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

Thanks Bernie.

How about the same thing with a different formula put into column "o"?
=if(g2>0,sum(h2:n2)," ")

Josh,

Range("E2:E" & Range("D65536").End(xlUp).Row).FormulaR1C1 = _
"=IF(RC[-1]>0,TODAY()-(RC[-1]+30),"" "")"

HTH,
Bernie
MS Excel MVP

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(D2>0,TODAY()-(D2+30)," ")
Fill into E3: =IF(D3>0,TODAY()-(D3+30)," ")
...Fill to last Row on sheet with data in column D.

 

[Guest]

Thanks Bernie. I had tried a similiar code, but it keep hanging up at the
formula. Works perfect!

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

Thanks Bernie.

How about the same thing with a different formula put into column "o"?
=if(g2>0,sum(h2:n2)," ")

Josh,

Range("E2:E" & Range("D65536").End(xlUp).Row).FormulaR1C1 = _
"=IF(RC[-1]>0,TODAY()-(RC[-1]+30),"" "")"

HTH,
Bernie
MS Excel MVP


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(D2>0,TODAY()-(D2+30)," ")
Fill into E3: =IF(D3>0,TODAY()-(D3+30)," ")
...Fill to last Row on sheet with data in column D.

 

[Bernie Deitrick]

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

 

[Guest]

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?

 

[Chip Pearson]

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" <[email protected]>
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?

Range("E2:E" & Range("D65536").End(xlUp).Row).FormulaR1C1 = _
"=IF(RC[-1]>0,TODAY()-(RC[-1]+30),"" "")"

 

[Katie]

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" & 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!

 

[Dave Peterson]

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???

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" & 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!

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

 

[Katie]

Dave -

Thank you, I'm an idiot.

I changed all the references and never changed where it looked. Thank you.

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???

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" & 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!

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]

Welcome to the club!!!

Dave -

Thank you, I'm an idiot.

I changed all the references and never changed where it looked. Thank you.

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???

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" & 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!

:

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!