Sales Schedule

[Howard]

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.

 

[royUK]

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

 

[Rick Rothstein]

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.

 

[Howard]

Thank you both!

Interesting idea about using a formula.
--
Howard

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)

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.

 

[Rick Rothstein]

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)

Thank you both!

Interesting idea about using a formula.
--
Howard

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)

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.

 

[royUK]

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