Inserting Rows Above Other Rows.

[stck2mlon]

Last Question for the day I promise.


I have a small VBA app that has Text Boxes and Option Buttons. The
insert into a spreadsheet using the following code.


Private Sub cmdOK_Click()
ActiveWorkbook.Sheets("Active Collection").Activate
Range("A1").Select

Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

If ActiveCell.Row <> 1 Then _
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1

ActiveCell.Offset(0, 1) = Date
ActiveCell.Offset(0, 2) = Time
ActiveCell.Offset(0, 3) = txtCompany.Value
ActiveCell.Offset(0, 4) = txtName.Value
ActiveCell.Offset(0, 5) = txtPhone.Value
ActiveCell.Offset(0, 6) = txtInvoiceNo.Value
ActiveCell.Offset(0, 7) = cmbInvoiceType.Value
ActiveCell.Offset(0, 8) = txtInvoiceDate.Value
ActiveCell.Offset(0, 9) = txtAmount.Value
ActiveCell.Offset(0, 10) = txtSubStartDate.Value
ActiveCell.Offset(0, 11) = txtWhichInvoice.Value
ActiveCell.Offset(0, 12) = txtPaid.Value

Select Case True
Case opt30.Value
ActiveCell.Offset(0, 13) = txtPaid.Value
Case opt60.Value
ActiveCell.Offset(0, 14) = txtPaid.Value
Case opt90.Value
ActiveCell.Offset(0, 15) = txtPaid.Value
Case opt120.Value
ActiveCell.Offset(0, 16) = txtPaid.Value
Case opt121.Value
ActiveCell.Offset(0, 17) = txtPaid.Value
End Select

ActiveCell.Offset(0, 18).Formula = "=SUM(RC14:RC18)"

Select Case True
Case optEOM.Value
ActiveCell.Offset(0, 19) = txtNextAmount.Value
Case optMOM.Value
ActiveCell.Offset(0, 20) = txtNextAmount.Value
End Select

ActiveCell.Offset(0, 21) = txtComments.Value

Range("A1").Select
Call frmNewCollect_Initialize
End Sub

___________________________________________________
My question is...Is there a way to have rows with formulas at th
bottom of this spreadsheet and each time the user adds a row from th
user form have it insert between the previous entry and the formul
rows at the bottom. In plain English, have the form be dynamic enoug
so that I don't have to have a fixed number of coulmns between firs
entered and formula row??

 

[stck2mlon]

No suggestions

 

[Bernie Deitrick]

Change all this:

Range("A1").Select

Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

To This:

Range("A65536").End(xlUp).Select
If Activecell.HasFormula Then Activecell.Entirerow.Insert

HTH,
Bernie
MS Excel MVP

 

[stck2mlon]

Thanks a ton again, you guys are really good.

 

[stck2mlon]

One quick question then...the formulas do not update with the new row
inserted...any suggestions

 

[Bernie Deitrick]

Instead of:

Range("A65536").End(xlUp).Select
If Activecell.HasFormula Then Activecell.Entirerow.Insert

Use this:

Range("A65536").End(xlUp).Select
If ActiveCell.HasFormula Then
ActiveCell(0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell(0).EntireRow
ActiveCell.EntireRow.ClearContents
End If

This may or may not work depending on whether your rows above the formula
row have links or other formulas.

HTH,
Bernie
MS Excel MVP