In data sheet serial number(1) repeats in each row???!!!

Atiqul Haque

New Member
Joined
Dec 13, 2020
Messages
45
Office Version
  1. 2016
Platform
  1. Windows
Dear All,

Into a table of my data sheet when i transfer data, serial number (1) repeats in each rows. Can anyone advice me where is the problem in my code:

Option Explicit
Private Sub CommandButton1_Click()

Dim rng As Range
Set rng = ActiveSheet.ListObjects("Table1").Range
Dim LastRow As Long
Dim erow As Long

erow = [Counta(Test!A:A)] + 1

LastRow = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

rng.Parent.Cells(LastRow + 1, 1).Value = erow - 1
rng.Parent.Cells(LastRow + 1, 2).Value = txtDate.Value
rng.Parent.Cells(LastRow + 1, 3).Value = txtName.Value
rng.Parent.Cells(LastRow + 1, 4).Value = cmbSex.Value
rng.Parent.Cells(LastRow + 1, 5).Value = [Text(Now(), "DD-MM-YYYY HH:MM:SS")]


'Reset the userform
txtDate.Value = ""
txtName.Value = ""
cmbSex.Value = ""
End Sub


Serial_No_Problem.JPG
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

R2ah1ze1l

Board Regular
Joined
Nov 10, 2017
Messages
67
Each time it runs erow-1 is the same value.
you need to the 1 to be an index to remove from erow so it too will shift dynamically.

All of your other items are coming from a celldestination.value

HTH
 

Watch MrExcel Video

Forum statistics

Threads
1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

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