Create Loop for each row in a column

smartpat19

Board Regular
Joined
Sep 3, 2014
Messages
114
I have the current macro (Code below) that adds one record to an access database which works great. How do I create a loop that would insert all 500 rows and stops at the next blank cell?

Or is there another option I should be looking at to insert multiple rows into access?

Code:
Sub Add_Record()


Application.ScreenUpdating = False


Dim db As database
Dim rs As DAO.Recordset


Set db = DAO.OpenDatabase("M:\Access\Development Team.accdb")
Set rs = db.OpenRecordset("Cost Schedule", dbOpenTable)






rs.AddNew
rs.Fields("Report_Name") = Range("A2").Value
rs.Fields("Project_Number") = Range("B2").Value
rs.Fields("Cost Month") = Range("C2").Value
rs.Fields("Acquisition Cost") = Range("D2").Value
rs.Fields("Hard Cost") = Range("E2").Value
rs.Update


rs.Close
db.Close


Application.ScreenUpdating = True




End Sub

Thank you
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi there. This is completely untested, but should work:
Code:
Sub Add_Record()


Application.ScreenUpdating = False


Dim db As database
Dim rs As DAO.Recordset


Set db = DAO.OpenDatabase("M:\Access\Development Team.accdb")
Set rs = db.OpenRecordset("Cost Schedule", dbOpenTable)

Dim LastRow As Long
Dim ThisRow As Long
' column A assumed to look for blanks

LastRow = Range("A65536").End(xlUp).Row
' works from row 2 to the last row with data in column A
For ThisRow = 2 To FromRow


    rs.AddNew
    rs.Fields("Report_Name") = Range("A" & ThisRow).Value
    rs.Fields("Project_Number") = Range("B" & ThisRow).Value
    rs.Fields("Cost Month") = Range("C" & ThisRow).Value
    rs.Fields("Acquisition Cost") = Range("D" & ThisRow).Value
    rs.Fields("Hard Cost") = Range("E" & ThisRow).Value
    rs.Update

Next ThisRow

rs.Close
db.Close


Application.ScreenUpdating = True




End Sub
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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
Back
Top