Fill in numbers macro in msaccess

Closed
akashneelh Posts 12 Registration date Wednesday September 19, 2012 Status Member Last seen January 5, 2013 - Oct 9, 2012 at 04:48 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Oct 10, 2012 at 05:46 AM
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
Related:

2 responses

akashneelh Posts 12 Registration date Wednesday September 19, 2012 Status Member Last seen January 5, 2013
Oct 9, 2012 at 09:39 AM
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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Oct 9, 2012 at 08:08 PM
whats there more to implement ? You just call this method by passing the two values.
0
akashneelh Posts 12 Registration date Wednesday September 19, 2012 Status Member Last seen January 5, 2013
Oct 9, 2012 at 11:50 PM
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.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Oct 10, 2012 at 05:46 AM
post your file at some public file sharing site and post back link to file back here
0
akashneelh Posts 12 Registration date Wednesday September 19, 2012 Status Member Last seen January 5, 2013
Oct 9, 2012 at 09:45 AM
Please help me guys