Enumeration

S

stck2mlon

Me again...

My small vba app adds a line to excel with information that is inpu
from date and time functions as well as user input. I want the firs
cell of the row to have an incrementing number. How would I have i
grab the number above it and then add 1 to it and input it into th
cell?

Thanks again for everyones help
 
B

Berend Botje

Should work with something like:

(Row X is inserted and colums Y has the increasing number. Increasemen
is Z)

Cells(X,Y).value = Cells(X-1,Y).value +
 
S

stck2mlon

So would this be correct? The red line is where I want it to go.

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

ActiveCell.Value = Cells(0, 0).Value = Cells(0 - 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) = "Invoice Amount"
ActiveCell.Offset(0, 19) = "Amount 1"
ActiveCell.Offset(0, 20) = "Amount 1"
ActiveCell.Offset(0, 21) = txtComments.Value

Range("A1").Select
Call frmNewCollect_Initialize
End Su
 
S

stck2mlon

So if I want it to pik up the first cell in row 13 and place it in 1
would it look like this.

ActiveCell.Value = Cells(0, 13).Value = Cells(0, 13).Value +
 
T

Tom Ogilvy

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