I have a sales schedule with
Col A Description
Col B Date Acquire
Col C Date Sold
Col D Proceeds
Col E Short-term Cost Basis
Col F Long-term Cost Basis
I just want one column for cost basis, so I inserted a column between E and
F and entered a formula: =IF(ISBLANK(E2),G2,E2). I copied the formula down to
F20. I did this as a macro. How can I make this flexible if I add rows (more
sales)? I've searched the site and have tried things like LastRow and
CurrentRegion, but I can't get anyting to work. How do I modify this code to
account for added rows?

ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-1]),RC[1],RC[-1])"
Selection.AutoFill Destination:=Range("F1:F20"), Type:=xlFillDefault
Range("F1:F20").Select


I'll really appreciate your help.
--
Howard

This will add the formula into Column F of the CurrentRegion, simply
overwrites the formula if it already exists

Code:
--------------------

Dim Rw As Long

Rw = Cells(2, 6).CurrentRegion.Rows.Count
Range(Cells(1, 6), Cells(Rw, 6)).Formula = "=IF(ISBLANK(RC[-1]),RC[1],RC[-1])"
--------------------


--
royUK

Hope that helps, RoyUK
For tips & examples visit 'my web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35184

Why use a macro? You could put this formula...

=IF(E2="",IF(G2="","",G2),E2)

in F2 and simply copy it down to whatever row you think is the maximum row
you might ever place data in. Nothing will be displayed in any row in Column
F unless there is a value in either Column E or Column G of the row.

--
Rick (MVP - Excel)


"Howard" wrote in message
...
I have a sales schedule with
Col A Description
Col B Date Acquire
Col C Date Sold
Col D Proceeds
Col E Short-term Cost Basis
Col F Long-term Cost Basis
I just want one column for cost basis, so I inserted a column between E
and
F and entered a formula: =IF(ISBLANK(E2),G2,E2). I copied the formula down
to
F20. I did this as a macro. How can I make this flexible if I add rows
(more
sales)? I've searched the site and have tried things like LastRow and
CurrentRegion, but I can't get anyting to work. How do I modify this code
to
account for added rows?

ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-1]),RC[1],RC[-1])"
Selection.AutoFill Destination:=Range("F1:F20"), Type:=xlFillDefault
Range("F1:F20").Select


I'll really appreciate your help.
--
Howard

Thank you both!

Interesting idea about using a formula.
--
Howard


"Rick Rothstein" wrote:

Why use a macro? You could put this formula...

=IF(E2="",IF(G2="","",G2),E2)

in F2 and simply copy it down to whatever row you think is the maximum row
you might ever place data in. Nothing will be displayed in any row in Column
F unless there is a value in either Column E or Column G of the row.

--
Rick (MVP - Excel)


"Howard" wrote in message
...
I have a sales schedule with
Col A Description
Col B Date Acquire
Col C Date Sold
Col D Proceeds
Col E Short-term Cost Basis
Col F Long-term Cost Basis
I just want one column for cost basis, so I inserted a column between E
and
F and entered a formula: =IF(ISBLANK(E2),G2,E2). I copied the formula down
to
F20. I did this as a macro. How can I make this flexible if I add rows
(more
sales)? I've searched the site and have tried things like LastRow and
CurrentRegion, but I can't get anyting to work. How do I modify this code
to
account for added rows?

ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-1]),RC[1],RC[-1])"
Selection.AutoFill Destination:=Range("F1:F20"), Type:=xlFillDefault
Range("F1:F20").Select


I'll really appreciate your help.
--
Howard

You are ultimately inserting a formula via your macro, so my question dealt
with bypassing the slow, inefficient VB code and place the formula directly
in the cell before hand. True, it puts formulas in cells where none is
required at the time, but Excel should not mind that too much (unless,
maybe, you are filling them to the bottom of the grid).

--
Rick (MVP - Excel)


"Howard" wrote in message
...
Thank you both!

Interesting idea about using a formula.
--
Howard


"Rick Rothstein" wrote:

Why use a macro? You could put this formula...

=IF(E2="",IF(G2="","",G2),E2)

in F2 and simply copy it down to whatever row you think is the maximum
row
you might ever place data in. Nothing will be displayed in any row in
Column
F unless there is a value in either Column E or Column G of the row.

--
Rick (MVP - Excel)


"Howard" wrote in message
...
I have a sales schedule with
Col A Description
Col B Date Acquire
Col C Date Sold
Col D Proceeds
Col E Short-term Cost Basis
Col F Long-term Cost Basis
I just want one column for cost basis, so I inserted a column between E
and
F and entered a formula: =IF(ISBLANK(E2),G2,E2). I copied the formula
down
to
F20. I did this as a macro. How can I make this flexible if I add rows
(more
sales)? I've searched the site and have tried things like LastRow and
CurrentRegion, but I can't get anyting to work. How do I modify this
code
to
account for added rows?

ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-1]),RC[1],RC[-1])"
Selection.AutoFill Destination:=Range("F1:F20"), Type:=xlFillDefault
Range("F1:F20").Select


I'll really appreciate your help.
--
Howard

I the add formula is not part of a routine and is part of a Table you
can set Excel to extend data formats & formulas from the Tools
menu-Options-Edit, this will then copy down formulas as you add to the
table. In 2003 you a create a List option from the Data menu for a
similar effect


--
royUK

Hope that helps, RoyUK
For tips & examples visit 'my web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35184