vendredi 20 novembre 2015

Issue with data match

I'm having an issue getting my loops correctly skip records. I have multiple records that need updating in a master from many slave workbooks. Each slave has a different name (of a it's user) and I need to make my "Update" loop more efficient by only comparing those records that contain the name of the User, then comparing the unique IDs, rather than what it currently does, which is compare all of the unique IDs to find a match.

My current command button simply looks for the users name in the column and if the count is higher than 0, it calls the module to update the records for that user.

In both Master and Slave column 1 is always the unique ID of the record and column 2 is always the user that the record is assigned to. Here is my current test coding (will become a template for other user's workbooks):

Option Explicit
Public Sub Agnes_Update()

 Dim owb As Workbook
 Dim Master, Slave As Worksheet
 Dim fpath As String
 Dim i, j As Integer

fpath = Application.ActiveWorkbook.Path & "\Agnes.xlsx"

Set owb = Application.Workbooks.Open(fpath)
Set Master = ThisWorkbook.Worksheets("Allocated")
Set Slave = owb.Worksheets("Work")


For j = 2 To 10 '(the master sheet)

For i = 2 To 10 '(the slave sheet)
    'if ID cell is blank exit - ends loop when all updates are completed
    If Trim(Slave.Cells(j, 1).Value2) = vbNullString Then Exit For 
    'if column 2 of master does not contain the current username being 
    'updated then move to next record
    If Master.Cells(j, 2).Value = "Agnes" Then  
    'if unique ID in column 1 matches slave from master then begin 
    'updating of required cells
    If Master.Cells(i, 1).Value2 = Slave.Cells(j, 1).Value2 Then
        Master.Cells(i, 4).Value = Slave.Cells(j, 4).Value
        Master.Cells(i, 5).Value = Slave.Cells(j, 5).Value
        Master.Cells(i, 6).Value = Slave.Cells(j, 6).Value
        Master.Cells(i, 7).Value = Slave.Cells(j, 7).Value
        Master.Cells(i, 8).Value = Slave.Cells(j, 8).Value
        Master.Cells(i, 9).Value = Slave.Cells(j, 9).Value
        Master.Cells(i, 10).Value = Slave.Cells(j, 10).Value
        Master.Cells(i, 11).Value = Slave.Cells(j, 11).Value
        Master.Cells(i, 12).Value = Slave.Cells(j, 12).Value
        Master.Cells(i, 13).Value = Slave.Cells(j, 13).Value
        Master.Cells(i, 14).Value = Slave.Cells(j, 14).Value
        Master.Cells(i, 15).Value = Slave.Cells(j, 15).Value
        Master.Cells(i, 16).Value = Slave.Cells(j, 16).Value
        Master.Cells(i, 17).Value = Slave.Cells(j, 17).Value
        Master.Cells(i, 18).Value = Slave.Cells(j, 18).Value
        Master.Cells(i, 19).Value = Slave.Cells(j, 19).Value
        Master.Cells(i, 20).Value = Slave.Cells(j, 20).Value
        Master.Cells(i, 21).Value = Slave.Cells(j, 21).Value
        Master.Cells(i, 22).Value = Slave.Cells(j, 22).Value
        Master.Cells(i, 23).Value = Slave.Cells(j, 23).Value
    End If
    End If
    Next
Next

Workbooks("Agnes").Close

End Sub

I prefer to use the Master.cells = Slave.cells method as some of the slave cells are locked to prevent the user amending data, and in the future the position of some of the data may change columns, so I will simply amend which master column = which slave column. I realize I could set the code to unlock the workbook, but that is even more coding for a simple bit of updating.

I believe the current issue with the code lies in the line If Master.Cells(j, 2).Value = "Agnes" Then as removing this line allows the code to go through all the unique IDs to find and update the master on all matches, but i'd rather it only try to match unique IDs when the user name is found in column 2 to try and make the code quicker and more efficient.

Can anyone help correct this code for me please?

Aucun commentaire:

Enregistrer un commentaire