Results 1 to 2 of 2

Thread: Create Loop for each row in a column

  1. #1
    Board Regular
    Join Date
    Sep 2014
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Create Loop for each row in a column

    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

  2. #2
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    633
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create Loop for each row in a column

    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
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •