Edit cells in master workbook based on matched values from current workbook

Question

The goal here is to compare values in column "A" between two workbooks (the current workbook is .xlsm, the target workbook is .xlsx). If any matches are found; the value in column "E" on the same row of matched value, is changed in the target workbook. It is mandatory to keep the workbooks separate in this case.

I decided to do this by selecting the first value in the current workbook (A2), applying it's value to a variable, then scanning column "A" in the target workbook to find a match (there should always be at least one match). Then changing the value of column "E" in the target workbook to "DSC" for those matched rows. Afterwards the selected cell in the current workbook is moved down one, and loops this process until a blank cell is reached.

Here is the code currently:

Sub DSC()
  Dim RowCount As Long

  secondWorkbook = "Master.xlsx"
  currentWorkbook = ThisWorkbook.Name
  Workbooks.Open ThisWorkbook.Path & "\" & secondWorkbook

  ' Define number of rows
  RowCount = Workbooks("Master.xlsx").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

  ' Select First Cell
  Windows(currentWorkbook).Activate
  Worksheets("Update Wipe").Activate
  Range("A2").Select
  Serial = ActiveCell.Value

  Windows(secondWorkbook).Activate
  Worksheets("Sheet1").Activate

  ' Run Function
  For c = 2 To (RowCount - 1)
    Windows(secondWorkbook).Activate
    If Sheet1.Cells(c, 1).Value = Serial Then
        Sheet1.Cells(c, 5) = "DSC"
        Windows(currentWorkbook).Activate
        Worksheets("Update Wipe").Activate
        Selection.Offset(1, 0).Select
        Serial = ActiveCell.Value
        If Serial = "" Then Exit For
    End If
  Next c
End Sub

At the moment no errors are returned, however nothing is updating in the target workbook. It will open the target workbook on the computer. Bouncing the active workbook and worksheet back and forth to change the selected cell and update the variable may be the cause.

I highly suggest reading through, and applying, [how to avoid using `.Select/`.Àctivate`](https://stackoverflow.com/questions/10714251/). It will likely help your code and may even be the fix you need. As you noted, "Bouncing the active workbook and worksheet back and forth ...may be the cause." — BruceWayne, May 09 '17 at 20:58
Just to confirm, the values don't need to be on the same row, right? For example, I have the word "test" in `A1` on `.xlsm` workbook - if I find test in `A580`, you want column `E` to read `DSC` ? — user1274820, May 09 '17 at 20:58
I gave you two examples - one that doesn't care which row the match is on, and one that only adds `DSC` if the row is the same when they match. Let me know if you need anything else. — user1274820, May 09 '17 at 21:27
@BruceWayne - Thanks, I did not come across this in my early research. It should help. — Tibs, May 09 '17 at 21:29

user1274820 · Accepted Answer · 2017-05-09T21:45:05.160

0

Here is a simplified working example.

You will want to change Range("A1:A20") to the range you want to compare.

You can also change Sheet("Sheet1") to the proper sheets.

AWorkbook is the .xlsm workbook.

MasterWorkbook is the .xlsx workbook.

As you can see, it's not necessary to use select.

You can loop through the cells using a For or For Each loop.

By nesting the for loops you can compare cells, though with larger datasets I would probably use Find and FindNext as opposed to looping through cells.

In this macro, I loop through each cell in our macro workbook, and compare it to each cell in the target workbook.

If the values match, I place DSC in the target workbook (column E)

Finally, close the workbook (SaveChanges:=True)

Sub DSC()
Dim AWorkbook, MasterWorkbook, c, d, ALastRow, MLastRow
Set AWorkbook = ThisWorkbook
Set MasterWorkbook = Workbooks.Open(ThisWorkbook.Path & "\" & "MasterWorkbook.xlsx", ReadOnly:=False)
ALastRow = AWorkbook.Sheets("Sheet1").Cells(Rows.CountLarge, "A").End(xlUp).Row
MLastRow = MasterWorkbook.Sheets("Sheet1").Cells(Rows.CountLarge, "A").End(xlUp).Row
For Each c In AWorkbook.Sheets("Sheet1").Range("A2:A" & ALastRow)
    For Each d In MasterWorkbook.Sheets("Sheet1").Range("A2:A" & MLastRow)
        If c.Value = d.Value Then MasterWorkbook.Sheets("Sheet1").Cells(d.Row, "E").Value = "DSC"
    Next d
Next c
MasterWorkbook.Close (True)
End Sub

edited May 09 '17 at 21:45

answered May 09 '17 at 21:04

user1274820

7,786
3
37
74

1

Your first example looks perfect. The values that match do not need to be on the same row, so it does exactly what is intended. – Tibs May 09 '17 at 21:31
Always happy to help! :) – user1274820 May 09 '17 at 21:32
The range you set ("A1:A20"), although I can change the range to be a static endpoint (A20), the length of the lists in both workbooks will fluctuate daily, which is why the rowcount was included in the original code. Would I be able to set the endpoint of the range to be dynamic and based off of a rowcount variable that is established earlier in the macro? Such as ("A1:A(rowcount)"). – Tibs May 09 '17 at 21:36
Yes, you did it in your original macro. I like usedrange. `.Range("A1:A" & AWorkbook.Sheets("Sheet1").UsedRange.Rows.CountLarge)` (on mobile ATM) – user1274820 May 09 '17 at 21:39
Note that usedrange doesn't take individual columns into account - You will want to use `.End(xlUp).Row` as you did originally if your data isn't square. – user1274820 May 09 '17 at 21:40
I updated the macro to make it easier on you - *updated again to correct because I'm slow and don't test my changes* – user1274820 May 09 '17 at 21:45
Updated a third time starting at `A2` because I think that's where you're at. – user1274820 May 09 '17 at 21:47
1

Yep, that worked flawlessly! This is just a small portion of what I need to do with this data, but the function here will carry over to the others just fine. Thank you again! – Tibs May 09 '17 at 21:58

score 0 · Answer 2 · answered May 09 '17 at 21:31

Load all of the values from Worksheets("Update Wipe")'s column A anto a dictionary. Open the Master workbook and Autofilter using the dictionary keys. Put "DSC" into all the visible cells in column E.

Option Explicit

Sub DSC()
    Dim mWB As Workbook
    Dim masterWorkbook As String, c As Long, rowCount As Long, serial As Variant
    Dim k As Long, dict As Object

    Set dict = CreateObject("Scripting.Dictionary")
    dict.comparemode = vbTextCompare

    With ThisWorkbook.Worksheets("Update Wipe")
        For k = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            dict(.Cells(k, "A").Value2) = vbNullString
        Next k
    End With

    masterWorkbook = "Master.xlsx"
    Set mWB = Workbooks.Open(ThisWorkbook.Path & "\" & masterWorkbook)

    With mWB.Worksheets("Sheet1")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, "A").CurrentRegion
            .AutoFilter field:=1, Criteria1:=dict.keys, Operator:=xlFilterValues
            With .Resize(.Rows.Count - 1, 5).Offset(1, 0)
                If CBool(Application.Subtotal(103, .Cells)) Then
                    .Columns("E").SpecialCells(xlCellTypeVisible) = "DSC"
                End If
            End With
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
        'optionally close the master workbook
        '.Parent.Close savechanges:=True
    End With
End Sub

Edit cells in master workbook based on matched values from current workbook

2 Answers2

Linked