Results 1 to 1 of 1

Macro to edit database based on values in adjacent cell

Threaded View

kayoke Macro to edit database based... 06-18-2012, 12:18 AM
  1. #1
    Registered User
    Join Date
    06-07-2012
    Location
    SG
    MS-Off Ver
    Excel 2007
    Posts
    37

    Macro to edit database based on values in adjacent cell

    I have a database for employees training records and have built a macro to update the database as such (see Update sheet). The Update sheet queries the database for the employee name (F9) from the Database (column A) and paste adjacent values (G9:R9) into the database column H:S.

    The current macro (SaveUpdate) does this perfectly only when the corresponding H:S is empty. When the columns are filled they overwrites the existing data (which I don't want that to happen). Instead, I would want the data to be pasted 1 row below the existing data (just like H9:S9). The question is how can I get this done? As the employees may or may not have existing training records and they may have a varying no. of existing records, my macro needs to be dynamic enough to achieve this.

    Current code is:
        Dim ws1 As Worksheet, ws2 As Worksheet, fAddress$
        Dim FoundOne As Range, LookInR As Range, LookForR As Range, c As Range
        Application.ScreenUpdating = False
        Set ws1 = Sheets("Database")
        Set ws2 = Sheets("Update")
        Set LookInR = Range(ws1.Range("A1"), ws1.Range("A" & Columns.Count).End(xlUp))
        Set LookForR = Range(ws2.Range("F9"), ws2.Range("F" & Rows.Count).End(xlUp))
        For Each c In LookForR
            With LookInR
                Set FoundOne = .Find(What:=c, LookAt:=xlPart)
                If Not FoundOne Is Nothing Then
                Do
                 fAddress = FoundOne.Address
                 FoundOne.Offset(, 7).Value = c.Offset(, 1).Value
                 FoundOne.Offset(, 8).Value = c.Offset(, 2).Value
                 FoundOne.Offset(, 9).Value = c.Offset(, 3).Value
                 FoundOne.Offset(, 10).Value = c.Offset(, 4).Value
                 FoundOne.Offset(, 11).Value = c.Offset(, 5).Value
                 FoundOne.Offset(, 12).Value = c.Offset(, 6).Value
                 FoundOne.Offset(, 13).Value = c.Offset(, 7).Value
                 FoundOne.Offset(, 14).Value = c.Offset(, 8).Value
                 FoundOne.Offset(, 15).Value = c.Offset(, 9).Value
                 FoundOne.Offset(, 16).Value = c.Offset(, 10).Value
                 FoundOne.Offset(, 17).Value = c.Offset(, 11).Value
                 FoundOne.Offset(, 18).Value = c.Offset(, 12).Value
                 Set FoundOne = .FindNext(After:=FoundOne)
                 Loop While FoundOne.Address <> fAddress
            End If
            End With
        Next c
        Set ws1 = Nothing
        Set ws2 = Nothing
        Set LookInR = Nothing: Set LookForR = Nothing
    Any help will be much appreciated.
    Attached Files Attached Files
    Last edited by kayoke; 06-19-2012 at 10:09 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1