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