VBA Rookie in need of assistance

achugg22

New Member
Joined
Jan 18, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi folks,

I am new to VBA coding and I am in need of some coding help. I am currently working on building a database and have created a UserForm to help input the data I want in quicker. I have got it working for the most part but when I enter the data it keeps going to the bottom of my table rather than the to the next row that is available. The code I have used for the "Add" Button in the UserForm is below. Any help would be appreciated and thanks in advance.

VBA Add Button code:

Private Sub cmdAdd_Click()
'Copy input values to sheet.
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Card Database")
iRow = ws.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
With ws
.Cells(iRow, 3).Value = Me.txtQty.Value
.Cells(iRow, 4).Value = Me.txtCardName.Value
.Cells(iRow, 5).Value = Me.cboCardType.Value
.Cells(iRow, 6).Value = Me.cboRarity.Value
.Cells(iRow, 7).Value = Me.txtSet.Value
.Cells(iRow, 8).Value = Me.txtValue.Value
End With
'Clear input controls.
Me.txtQty.Value = ""
Me.txtCardName.Value = ""
Me.cboCardType.Value = ""
Me.cboRarity.Value = ""
Me.txtSet.Value = ""
Me.txtValue.Value = ""
End Sub
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,052
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
If you step through the code using F8 when you get to the iRow = line hover the mouse over Irow and tell us what number is designated to iRow ?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,052
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Ok, your reference to iRow will need to be in a column that has data, currnetly you are referencing col "C"
Rich (BB code):
iRow = ws.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
If the sheet is blank to start with you could add a line of code to start irow at at least say row 1
VBA Code:
iRow = ws.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
If irow =0 then irow = 1
 

achugg22

New Member
Joined
Jan 18, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok, your reference to iRow will need to be in a column that has data, currnetly you are referencing col "C"
Rich (BB code):
iRow = ws.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
If the sheet is blank to start with you could add a line of code to start irow at at least say row 1
VBA Code:
iRow = ws.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
If irow =0 then irow = 1
I tried adding that and it still added the new entry in the end of the table and I also noticed that it is also adding another line to the table.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,052
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Can you post a sample of your input data....use the XL2BB to post your sample ( see my rag)
 

achugg22

New Member
Joined
Jan 18, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Can you post a sample of your input data....use the XL2BB to post your sample ( see my rag)
Yu-Gi-Oh Card Collection Database.xlsm
CDEFGH
5QtyCard NameCard TypeRaritySetValue
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Card Database
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C6:H5010Expression=$E6="Trap"textNO
C6:H5010Expression=$E6="Spell"textNO
C6:H5010Expression=$E6="Link"textNO
C6:H5010Expression=$E6="Xyz Pendulum"textNO
C6:H5010Expression=$E6="Synchro Pendulum"textNO
C6:H5010Expression=$E6="Fusion Pendulum"textNO
C6:H5010Expression=$E6="Effect Pendulum"textNO
C6:H5010Expression=$E6="Normal Pendulum"textNO
C6:H5010Expression=$E6="Xyz"textNO
C6:H5010Expression=$E6="Synchro"textNO
C6:H5010Expression=$E6="Fusion"textNO
C6:H5010Expression=$E6="Ritual"textNO
C6:H5010Expression=$E6="Effect"textNO
C6:H5010Expression=$E6="Normal"textNO
 

achugg22

New Member
Joined
Jan 18, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Yu-Gi-Oh Card Collection Database.xlsm
CDEFGH
5QtyCard NameCard TypeRaritySetValue
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Card Database
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C6:H5010Expression=$E6="Trap"textNO
C6:H5010Expression=$E6="Spell"textNO
C6:H5010Expression=$E6="Link"textNO
C6:H5010Expression=$E6="Xyz Pendulum"textNO
C6:H5010Expression=$E6="Synchro Pendulum"textNO
C6:H5010Expression=$E6="Fusion Pendulum"textNO
C6:H5010Expression=$E6="Effect Pendulum"textNO
C6:H5010Expression=$E6="Normal Pendulum"textNO
C6:H5010Expression=$E6="Xyz"textNO
C6:H5010Expression=$E6="Synchro"textNO
C6:H5010Expression=$E6="Fusion"textNO
C6:H5010Expression=$E6="Ritual"textNO
C6:H5010Expression=$E6="Effect"textNO
C6:H5010Expression=$E6="Normal"textNO
I don't know if this helps or not.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,052
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Not really, can you upload the workbook to a hosting site, like dropbox, then post a link back here...someone can then take a look at it
 

Watch MrExcel Video

Forum statistics

Threads
1,127,859
Messages
5,627,293
Members
416,236
Latest member
Lynchbox

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
Top