VBA HELP: Problem entering data to Excel table with Form

sooshil

Board Regular
Joined
Feb 21, 2013
Messages
104
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.
Code:
    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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Say your table name is Table1, try this:
Code:
[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]
 
Upvote 0
Similar to @Akuini

Code:
    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]
 
Upvote 0
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.
 
Upvote 0
Say your table name is Table1, try this:
Code:
[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.
 
Upvote 0
Similar to @Akuini

Code:
    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.
 
Upvote 0
Upvote 0
Reply to post#6
BAD
Code:
ws.Range("A" & [COLOR=#ff0000]n + 1)[/COLOR].Value = Me.cboTeam1

GOOD
Code:
ws.Range("A" & [COLOR=#ff0000]n[/COLOR]).Value = Me.cboTeam1
 
Last edited:
Upvote 0
Reply to post#6
BAD
Code:
ws.Range("A" & [COLOR=#ff0000]n + 1)[/COLOR].Value = Me.cboTeam1

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


YEAH... finally. That worked.

Thanks a lot for you both. God bless you.
 
Upvote 0

Forum statistics

Threads
1,223,703
Messages
6,173,973
Members
452,540
Latest member
haasro02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top