Fill in numbers macro in msaccess

[Closed]
Report
Posts
12
Registration date
Wednesday September 19, 2012
Status
Member
Last seen
January 5, 2013
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,
I have list of numbers in range.
and I am using a macro to extract the middle serial nos.
My range is like
START_SR_NO END_SR_NO COUNT
205113064000 205113065999 2000
406116482000 406116483999 2000
205113074000 205113075999 2000
205114221000 205114222999 2000
205114233000 205114234999 2000
205113110000 205113111999 2000

and I am using the below vb macro.My requirement is that due to low data capacity excel can handle only upto 10 lakhs ,wheras i have a data of 30 crores like this.
Please help me implementing this macro in MSaccess or any other way.

Please help.

I am using.


Public Sub Fillinnumbers()

Dim sourceSheetName As String
Dim desitinationSheetName As String

Dim startPoint As Double
Dim endPoint As Double
Dim maximumSourceRow As Long
Dim processRow As Long
Dim sourceRow As Long
Dim deltaRow As Double

sourceSheetName = "Sheet1"
desitinationSheetName = "Sheet2"

With Sheets(sourceSheetName)
maximumSourceRow = getItemLocation("*", .Range(.Cells(1, "A"), .Cells(.Rows.Count, "B")))
If (maximumSourceRow < 2) Then Exit Sub
End With

With Sheets(desitinationSheetName)
.Columns(1).Clear
.Columns(1).NumberFormat = "#"
processRow = 0
For sourceRow = 2 To maximumSourceRow
startPoint = Sheets(sourceSheetName).Cells(sourceRow, "A")
endPoint = Sheets(sourceSheetName).Cells(sourceRow, "B")
deltaRow = endPoint - startPoint
processRow = processRow + 1
.Cells(processRow, "A").Value = startPoint
.Cells(processRow, "A").AutoFill Destination:=.Range(.Cells(processRow, "A"), .Cells(processRow + deltaRow, "A")), Type:=xlFillSeries
processRow = processRow + deltaRow
Next sourceRow
End With






End Sub

2 replies

Posts
12
Registration date
Wednesday September 19, 2012
Status
Member
Last seen
January 5, 2013

I have found this function .But how to implement this.

Public Sub CreateSequence(ByVal lngStartValue As Long, lngStopValue As Long)


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter As Long

Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("Table2", dbOpenTable)

For lngCounter = lngStartValue To lngStopValue
rs.AddNew
rs.Fields("serial") = lngCounter
'---add any other values here if you want.
rs.Update
Next lngCounter

rs.Close
Set rs = Nothing
End Sub
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
whats there more to implement ? You just call this method by passing the two values.
Posts
12
Registration date
Wednesday September 19, 2012
Status
Member
Last seen
January 5, 2013

But I am unable to get the result.It is not working in Module.When I try to run the function from VB window,i shows some argument error.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
post your file at some public file sharing site and post back link to file back here
Posts
12
Registration date
Wednesday September 19, 2012
Status
Member
Last seen
January 5, 2013

Please help me guys