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??

--
Message posted from http://www.ExcelForum.com

No suggestions

--
Message posted from http://www.ExcelForum.com

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 " wrote in message
...
Last Question for the day I promise.


I have a small VBA app that has Text Boxes and Option Buttons. They
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 the
bottom of this spreadsheet and each time the user adds a row from the
user form have it insert between the previous entry and the formula
rows at the bottom. In plain English, have the form be dynamic enough
so that I don't have to have a fixed number of coulmns between first
entered and formula row???


---
Message posted from http://www.ExcelForum.com/

Thanks a ton again, you guys are really good.


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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

"stck2mlon " wrote in message
...
One quick question then...the formulas do not update with the new rows
inserted...any suggestions?


---
Message posted from http://www.ExcelForum.com/