VBA Rookie in need of assistance

[achugg22]

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

 

[Michael M]

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 ?

 

[achugg22]

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 ?

iRow=0

 

[Michael M]

Ok, your reference to iRow will need to be in a column that has data, currnetly you are referencing col "C"

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

iRow = ws.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
If irow =0 then irow = 1

 

[achugg22]

Ok, your reference to iRow will need to be in a column that has data, currnetly you are referencing col "C"

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

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]

Can you post a sample of your input data....use the XL2BB to post your sample ( see my rag)

 

[achugg22]

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
	C	D	E	F	G	H
5	Qty	Card Name	Card Type	Rarity	Set	Value
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
Cell	Condition		Cell Format	Stop If True
C6:H5010	Expression	=$E6="Trap"	text	NO
C6:H5010	Expression	=$E6="Spell"	text	NO
C6:H5010	Expression	=$E6="Link"	text	NO
C6:H5010	Expression	=$E6="Xyz Pendulum"	text	NO
C6:H5010	Expression	=$E6="Synchro Pendulum"	text	NO
C6:H5010	Expression	=$E6="Fusion Pendulum"	text	NO
C6:H5010	Expression	=$E6="Effect Pendulum"	text	NO
C6:H5010	Expression	=$E6="Normal Pendulum"	text	NO
C6:H5010	Expression	=$E6="Xyz"	text	NO
C6:H5010	Expression	=$E6="Synchro"	text	NO
C6:H5010	Expression	=$E6="Fusion"	text	NO
C6:H5010	Expression	=$E6="Ritual"	text	NO
C6:H5010	Expression	=$E6="Effect"	text	NO
C6:H5010	Expression	=$E6="Normal"	text	NO

 

[achugg22]

Yu-Gi-Oh Card Collection Database.xlsm
	C	D	E	F	G	H
5	Qty	Card Name	Card Type	Rarity	Set	Value
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
Cell	Condition		Cell Format	Stop If True
C6:H5010	Expression	=$E6="Trap"	text	NO
C6:H5010	Expression	=$E6="Spell"	text	NO
C6:H5010	Expression	=$E6="Link"	text	NO
C6:H5010	Expression	=$E6="Xyz Pendulum"	text	NO
C6:H5010	Expression	=$E6="Synchro Pendulum"	text	NO
C6:H5010	Expression	=$E6="Fusion Pendulum"	text	NO
C6:H5010	Expression	=$E6="Effect Pendulum"	text	NO
C6:H5010	Expression	=$E6="Normal Pendulum"	text	NO
C6:H5010	Expression	=$E6="Xyz"	text	NO
C6:H5010	Expression	=$E6="Synchro"	text	NO
C6:H5010	Expression	=$E6="Fusion"	text	NO
C6:H5010	Expression	=$E6="Ritual"	text	NO
C6:H5010	Expression	=$E6="Effect"	text	NO
C6:H5010	Expression	=$E6="Normal"	text	NO

I don't know if this helps or not.

 

[Michael M]

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