Hello,

I want to write a program that will search down a column to find the first
empty cell, insert a row at that point, and then perform sums for certain
columns. Also, I want to create line formats for each column. The first empty
cell occurrence will be variable, but the columns will be fixed....Thanks!

Assuming the column will never be blank or have less than 2 rows, and
headers are in row1.

Sub AddFormulas()
Cells(1,1).End(xldown).offset(1,0).Range( _
"A1,C1,F1:J1").formulaR1C1 = "=Sum(R2C:R[-1]C)"
End Sub

adds formulas in column A, C, F through J. Adjust to suit.
--
Regards,
Tom Ogilvy

"Matt" wrote in message
...
Hello,

I want to write a program that will search down a column to find the first
empty cell, insert a row at that point, and then perform sums for certain
columns. Also, I want to create line formats for each column. The first
empty
cell occurrence will be variable, but the columns will be fixed....Thanks!

Thanks much Tom....does the End(xldown) bring me to the last cell of the
column with anything in it? And which piece of the code inserts a new row?
Thanks!!

"Tom Ogilvy" wrote:

Assuming the column will never be blank or have less than 2 rows, and
headers are in row1.

Sub AddFormulas()
Cells(1,1).End(xldown).offset(1,0).Range( _
"A1,C1,F1:J1").formulaR1C1 = "=Sum(R2C:R[-1]C)"
End Sub

adds formulas in column A, C, F through J. Adjust to suit.
--
Regards,
Tom Ogilvy

"Matt" wrote in message
...
Hello,

I want to write a program that will search down a column to find the first
empty cell, insert a row at that point, and then perform sums for certain
columns. Also, I want to create line formats for each column. The first
empty
cell occurrence will be variable, but the columns will be fixed....Thanks!

Added Tom's code for inserting formuals into this, might not look
pretty and prob abaly smarter ways of doing it but...

Sub AddFormulas()

Range("A1").Select

Do Until Cells(ActiveCell.Row + 1, 1) = "" And Cells(ActiveCell.Row +
2, 1) = ""

If ActiveCell = "" Then
Selection.EntireRow.Insert
ActiveCell.Range( _
"C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)"
ActiveCell.Offset(2, 0).Select

Else
Cells(ActiveCell.Row + 1, 1).Select
End If
Loop
ActiveCell = ActiveCell.Offset(1, 0)
Selection.EntireRow.Insert
ActiveCell.Range( _
"C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)"
ActiveCell.Offset(2, 0).Select

End Sub

Just change the starting cell ie Range("A1").select to suit and change
the formuals in

ActiveCell.Range( _
"C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)"

to suit.

Regards

Stopher

Stopher wrote:
Added Tom's code for inserting formuals into this, might not look
pretty and prob abaly smarter ways of doing it but...

Sub AddFormulas()

Range("A1").Select

Do Until Cells(ActiveCell.Row + 1, 1) = "" And Cells(ActiveCell.Row +
2, 1) = ""

If ActiveCell = "" Then
Selection.EntireRow.Insert
ActiveCell.Range( _
"C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)"
ActiveCell.Offset(2, 0).Select

Else
Cells(ActiveCell.Row + 1, 1).Select
End If
Loop
ActiveCell = ActiveCell.Offset(1, 0)
Selection.EntireRow.Insert
ActiveCell.Range( _
"C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)"
ActiveCell.Offset(2, 0).Select

End Sub

Just change the starting cell ie Range("A1").select to suit and change
the formuals in

ActiveCell.Range( _
"C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)"

to suit.

Regards

Stopher

If you wanted it just for the first contiguous block of data then:

Sub AddFormulas2()

Range("A1").Select

Range(Selection, Selection.End(xlDown)).Select

Cells(Selection.Item(Selection.Count), 1).Select

Selection.EntireRow.Insert
ActiveCell.Range( _
"C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)"
ActiveCell.Offset(2, 0).Select

End Sub

Once again change you starting cell Range("A1").Select to suit

Stopher

Stopher wrote:
Stopher wrote:
Added Tom's code for inserting formuals into this, might not look
pretty and prob abaly smarter ways of doing it but...

Sub AddFormulas()

Range("A1").Select

Do Until Cells(ActiveCell.Row + 1, 1) = "" And Cells(ActiveCell.Row +
2, 1) = ""

If ActiveCell = "" Then
Selection.EntireRow.Insert
ActiveCell.Range( _
"C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)"
ActiveCell.Offset(2, 0).Select

Else
Cells(ActiveCell.Row + 1, 1).Select
End If
Loop
ActiveCell = ActiveCell.Offset(1, 0)
Selection.EntireRow.Insert
ActiveCell.Range( _
"C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)"
ActiveCell.Offset(2, 0).Select

End Sub

Just change the starting cell ie Range("A1").select to suit and change
the formuals in

ActiveCell.Range( _
"C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)"

to suit.

Regards

Stopher

If you wanted it just for the first contiguous block of data then:

Sub AddFormulas2()

Range("A1").Select

Range(Selection, Selection.End(xlDown)).Select

Cells(Selection.Item(Selection.Count), 1).Select

Selection.EntireRow.Insert
ActiveCell.Range( _
"C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)"
ActiveCell.Offset(2, 0).Select

End Sub

Once again change you starting cell Range("A1").Select to suit

Stopher

Hmmm worked the first time I tried it, seems to want to go to row
38764......

Sub AddFormulas2()

Range("A1").Select

Range(Selection, Selection.End(xlDown)).Select
MsgBox Selection.Count
Cells((Selection.Count + 1), 1).Select

Selection.EntireRow.Insert
ActiveCell.Range( _
"C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)"
ActiveCell.Offset(2, 0).Select

End Sub


Try this

I am trying this and getting a 400 error...not really sure what that means?

"Tom Ogilvy" wrote:

Assuming the column will never be blank or have less than 2 rows, and
headers are in row1.

Sub AddFormulas()
Cells(1,1).End(xldown).offset(1,0).Range( _
"A1,C1,F1:J1").formulaR1C1 = "=Sum(R2C:R[-1]C)"
End Sub

adds formulas in column A, C, F through J. Adjust to suit.
--
Regards,
Tom Ogilvy

"Matt" wrote in message
...
Hello,

I want to write a program that will search down a column to find the first
empty cell, insert a row at that point, and then perform sums for certain
columns. Also, I want to create line formats for each column. The first
empty
cell occurrence will be variable, but the columns will be fixed....Thanks!

Sub AddFormulas()
Dim rng as Range
Set rng = Cells(1, 1).End(xlDown).Offset(1, 0)
rng.Range( _
"A1,C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)"
rng.EntireRow.Insert
End Sub

the original worked fine for me. This one inserts a line above the
formulas. It worked fine for me as well.

--
Regards,
Tom Ogilvy



"Matt" wrote in message
...
I am trying this and getting a 400 error...not really sure what that means?

"Tom Ogilvy" wrote:

Assuming the column will never be blank or have less than 2 rows, and
headers are in row1.

Sub AddFormulas()
Cells(1,1).End(xldown).offset(1,0).Range( _
"A1,C1,F1:J1").formulaR1C1 = "=Sum(R2C:R[-1]C)"
End Sub

adds formulas in column A, C, F through J. Adjust to suit.
--
Regards,
Tom Ogilvy

"Matt" wrote in message
...
Hello,

I want to write a program that will search down a column to find the
first
empty cell, insert a row at that point, and then perform sums for
certain
columns. Also, I want to create line formats for each column. The first
empty
cell occurrence will be variable, but the columns will be
fixed....Thanks!