putting an equation in a column?

[xtscars]

So far I have learned how to copy and paste my final column in VB. Now, how do I tell it to create an equation in finalcolumn+1? The equation is simply the difference in the prior two columns.

a b c d
1 x y z =z-y
2 x y z =z-y
3 x y z =z-y

how do I code that last column d to put that equation in?

Thanks!

 

[VoG]

Try like this

range("D1:D100").FormulaR1C1="=RC[-1]-RC[-2]"

 

[xtscars]

thank you! but instead of hard coding that D range, how can I get it to always be finalcolumn+1? Can I just put range(finalcolumn+1)?

 

[VoG]

Try

range(Cells(1,finalcolumn+1),cells(100,finalcolumn+1)).FormulaR1C1="=RC[-1]-RC[-2]"

 

[xtscars]

thank you for the help! that worked great. Now, my only problem is that there are some cells that need to be deleted because there aren't any cells to the left of it for the equation to calculate. Do you have any ideas for how I can use variables to delete those cells? If this doesn't make sense, let me know and I'll try to be more specific. Thanks!

 

[VoG]

Take a look at range.SpecialCells(xlCellTypeBlanks)

 

[xtscars]

would you be willing to give me a little more guidance on how to use that code? I would appreciate it. Thanks again.

 

[VoG]

The code would be like this

Range(Cells(1, lastcol), Cells(100, lastcol)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete