Age in Userform automatic update

Hi, I have a table with different entries, I fill this table using a Userform, so I don't have a fixed number of rows; at the end of the table I put 2 columns:

the first calculate the age based to the date of birth (FORMULA =DATEDIF(K2;TODAY();"Y") )

the seconf column say if the guy is adult or not (FORMULA =IF(P2>=16;"adult";"young") )

I extend the formula to the whole column, but...

the first of this 2 columns is filled with 115 and the second with adult. Because I need to say how many adult and young there is in my table (and at the moment I have every cell with adult I'm trying to extend this formulas only after I fill a new row.

I have an idea, if I add to my code to write the formulas in the cell? Or if you have any ideas they are welkome

RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
    
    With Worksheets("Sheet1").Range("A1")
        .Offset(RowCount, 0).Value = Me.lblA.Caption
        .Offset(RowCount, 1).Value = Me.txtB.Value
        .Offset(RowCount, 2).Value = Me.txtC.Value
    Select Case True
        Case Me.optF
        .Offset(RowCount, 3).Value = "F"
        Case Me.optM
        .Offset(RowCount, 3).Value = "M"
    End Select
        .Offset(RowCount, 4).Value = Me.txtD.Value
    If Me.chkS.Value = True Then
        .Offset(RowCount, 5).Value = "yes"
    Else
        .Offset(RowCount, 5).Value = "no"
    End If
        .Offset(RowCount, 6).Value = Me.cboT.Value
    If Me.chkCAR.Value = True Then
        .Offset(RowCount, 7).Value = "yes"
    Else
        .Offset(RowCount, 7).Value = "no"
    End If
        .Offset(RowCount, 8).Value = Me.txtI.Value
        .Offset(RowCount, 9).Value = Me.txtQ.Value
        .Offset(RowCount, 10).Value = DateValue(Me.txtDOB.Value)
        .Offset(RowCount, 11).Value = Me.txtCOB.Value
    If Me.txtCF1.Value = "" Then
        .Offset(RowCount, 12).Value = Me.txtCF2.Value
    Else
        .Offset(RowCount, 12).Value = Me.txtCF1.Value
    End If
        .Offset(RowCount, 13).Value = Me.txtPH.Value
        .Offset(RowCount, 14).Value = Me.txtEM.Value


HERE I THINK TO ADD THE FORMULAS (IN COLUMNS 15 AND 16)




      End With

Thanks for your help

Re: Age in Userform automatic update

here

forum.ozgrid.com/index.php?attachment/66219/

Re: Age in Userform automatic update

Up

Re: Age in Userform automatic update

It works, but not as in your example. When I add something new it creates a new row with all the data, but, it also add another row with just the formulas in the last 2 cells. So it add 2 rows everytime.

Quote from royUK;752575

Covert the data to a Table format

Overview of Tables

Change the code in the UserForm to this

Code

Option Explicit
Private Sub CommandButton1_Click()
    Dim oTbl As ListObject
    Dim ONewRw As ListRow
    Dim oCtl As Control
    Dim iX As Integer




    ' Scrittura campi in excel
    ' Reset campi
    Set oTbl = Sheet1.ListObjects(1)
    Set ONewRw = oTbl.ListRows.Add(AlwaysInsert:=True)


    iX = 1


    For Each oCtl In Me.Controls
        If TypeName(oCtl) = "TextBox" Then
            ONewRw.Range.Cells(1, iX).Value = oCtl.Value
            oCtl.Value = ""
            iX = iX + 1
        End If
    Next oCtl


End Sub

Display More

Display More