VBA HELP: Problem entering data to Excel table with Form

[sooshil]

Hi,

I am trying to enter data through a form to an Excel table.
But the the data is not going to the table but it starts just below the table and the data entered doesn't belong to the table. All data entry procedures works fine. Just it doesn't enter into the table.
Here is the code I used for the button to enter data.

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("MatchData")
    Dim n As Long
    
    'Find the last available row
    n = ws.Range("A" & Application.Rows.Count).End(xlUp).Row
    
        ws.Range("A" & n + 1).Value = Me.cboTeam1
        ws.Range("B" & n + 1).Value = Me.cboTeam2
        ws.Range("C" & n + 1).Value = CInt(Me.txtTeam1Run)
        ws.Range("D" & n + 1).Value = CDbl(Me.txtTeam1Over)
        ws.Range("E" & n + 1).Value = CInt(Me.txtTeam2Run)
        ws.Range("F" & n + 1).Value = CDbl(Me.txtTeam2Over)
        ws.Range("G" & n + 1).Value = Me.chkTie
        ws.Range("H" & n + 1).Value = Me.chkNR

What can I edit here to fulfill my requirement?
Thanks a lot

 

[Akuini]

Say your table name is Table1, try this:

[FONT=Lucida Console]    [COLOR=Royalblue]Dim[/COLOR] ws [COLOR=Royalblue]As[/COLOR] Worksheet
    [COLOR=Royalblue]Set[/COLOR] ws = ThisWorkbook.Sheets([COLOR=Darkcyan]"MatchData"[/COLOR])
    [COLOR=Royalblue]Dim[/COLOR] n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
    
    [I][COLOR=Dimgray]'add a new row to the bottom of the table[/COLOR][/I]
    ws.ListObjects([COLOR=Darkcyan]"Table1"[/COLOR]).ListRows.Add
    
    [I][COLOR=Dimgray]'Find the last available row[/COLOR][/I]
    n = ws.Range([COLOR=Darkcyan]"A"[/COLOR] & Rows.Count).[COLOR=Royalblue]End[/COLOR](xlUp).Row
    
        ws.Range([COLOR=Darkcyan]"A"[/COLOR] & n).Value = [COLOR=Royalblue]Me[/COLOR].cboTeam1
        ws.Range([COLOR=Darkcyan]"B"[/COLOR] & n).Value = [COLOR=Royalblue]Me[/COLOR].cboTeam2
        ws.Range([COLOR=Darkcyan]"C"[/COLOR] & n).Value = [COLOR=Royalblue]CInt[/COLOR]([COLOR=Royalblue]Me[/COLOR].txtTeam1Run)
        ws.Range([COLOR=Darkcyan]"D"[/COLOR] & n).Value = [COLOR=Royalblue]CDbl[/COLOR]([COLOR=Royalblue]Me[/COLOR].txtTeam1Over)
        ws.Range([COLOR=Darkcyan]"E"[/COLOR] & n).Value = [COLOR=Royalblue]CInt[/COLOR]([COLOR=Royalblue]Me[/COLOR].txtTeam2Run)
        ws.Range([COLOR=Darkcyan]"F"[/COLOR] & n).Value = [COLOR=Royalblue]CDbl[/COLOR]([COLOR=Royalblue]Me[/COLOR].txtTeam2Over)
        ws.Range([COLOR=Darkcyan]"G"[/COLOR] & n).Value = [COLOR=Royalblue]Me[/COLOR].chkTie
        ws.Range([COLOR=Darkcyan]"H"[/COLOR] & n).Value = [COLOR=Royalblue]Me[/COLOR].chkNR[/FONT]

 

[Yongle]

Similar to @Akuini

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("MatchData")
    Dim n As Long
    [COLOR=#ff0000]Dim tblRow As ListRow[/COLOR]
'add another row to table and use it
[COLOR=#ff0000]    Set tblRow = ws.ListObjects(1).ListRows.Add
    n = tblRow.Range.Row[/COLOR]

    ws.Range("A" & n + 1).Value = Me.cboTeam1
    ws.Range("B" & n + 1).Value = Me.cboTeam2
[COLOR=#006400]'etc[/COLOR]

 

[sooshil]

The data entered now is the part of the table, but the data entered into the alternative rows of the table. Data goes into the every next row leaving an empty row every time.

 

[sooshil]

Say your table name is Table1, try this:

[FONT=Lucida Console]    [COLOR=Royalblue]Dim[/COLOR] ws [COLOR=Royalblue]As[/COLOR] Worksheet
    [COLOR=Royalblue]Set[/COLOR] ws = ThisWorkbook.Sheets([COLOR=Darkcyan]"MatchData"[/COLOR])
    [COLOR=Royalblue]Dim[/COLOR] n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
    
    [I][COLOR=Dimgray]'add a new row to the bottom of the table[/COLOR][/I]
    ws.ListObjects([COLOR=Darkcyan]"Table1"[/COLOR]).ListRows.Add
    
    [I][COLOR=Dimgray]'Find the last available row[/COLOR][/I]
    n = ws.Range([COLOR=Darkcyan]"A"[/COLOR] & Rows.Count).[COLOR=Royalblue]End[/COLOR](xlUp).Row
    
        ws.Range([COLOR=Darkcyan]"A"[/COLOR] & n).Value = [COLOR=Royalblue]Me[/COLOR].cboTeam1
        ws.Range([COLOR=Darkcyan]"B"[/COLOR] & n).Value = [COLOR=Royalblue]Me[/COLOR].cboTeam2
        ws.Range([COLOR=Darkcyan]"C"[/COLOR] & n).Value = [COLOR=Royalblue]CInt[/COLOR]([COLOR=Royalblue]Me[/COLOR].txtTeam1Run)
        ws.Range([COLOR=Darkcyan]"D"[/COLOR] & n).Value = [COLOR=Royalblue]CDbl[/COLOR]([COLOR=Royalblue]Me[/COLOR].txtTeam1Over)
        ws.Range([COLOR=Darkcyan]"E"[/COLOR] & n).Value = [COLOR=Royalblue]CInt[/COLOR]([COLOR=Royalblue]Me[/COLOR].txtTeam2Run)
        ws.Range([COLOR=Darkcyan]"F"[/COLOR] & n).Value = [COLOR=Royalblue]CDbl[/COLOR]([COLOR=Royalblue]Me[/COLOR].txtTeam2Over)
        ws.Range([COLOR=Darkcyan]"G"[/COLOR] & n).Value = [COLOR=Royalblue]Me[/COLOR].chkTie
        ws.Range([COLOR=Darkcyan]"H"[/COLOR] & n).Value = [COLOR=Royalblue]Me[/COLOR].chkNR[/FONT]

The data entered now is the part of the table, but the data entered into the alternative rows of the table. Data goes into the every next row leaving an empty row every time.

 

[sooshil]

Similar to @Akuini

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("MatchData")
    Dim n As Long
    [COLOR=#ff0000]Dim tblRow As ListRow[/COLOR]
'add another row to table and use it
[COLOR=#ff0000]    Set tblRow = ws.ListObjects(1).ListRows.Add
    n = tblRow.Range.Row[/COLOR]

    ws.Range("A" & n + 1).Value = Me.cboTeam1
    ws.Range("B" & n + 1).Value = Me.cboTeam2
[COLOR=#006400]'etc[/COLOR]

Same Problem.
The data entered now is the part of the table, but the data entered into the alternative rows of the table. Data goes into the every next row leaving an empty row every time.

 

[Akuini]

The data entered now is the part of the table, but the data entered into the alternative rows of the table. Data goes into the every next row leaving an empty row every time.

Hm, I don't know why that happened.
The "ListRows.Add" should only add 1 row at the bottom, please read this article:
https://docs.microsoft.com/en-us/office/vba/api/excel.listrows.add

Could you upload your workbook to a free site (such as dropbox.com or google drive)?

 

[Yongle]

Reply to post#6
BAD

ws.Range("A" & [COLOR=#ff0000]n + 1)[/COLOR].Value = Me.cboTeam1

GOOD

ws.Range("A" & [COLOR=#ff0000]n[/COLOR]).Value = Me.cboTeam1

 

[sooshil]

Reply to post#6
BAD

ws.Range("A" & [COLOR=#ff0000]n + 1)[/COLOR].Value = Me.cboTeam1

GOOD

ws.Range("A" & [COLOR=#ff0000]n[/COLOR]).Value = Me.cboTeam1

YEAH... finally. That worked.

Thanks a lot for you both. God bless you.