根据当前工作簿中的匹配值编辑主工作簿中的单元格 [英] Edit cells in master workbook based on matched values from current workbook

问题描述

此处的目标是比较两个工作簿之间的"A"列中的值(当前工作簿为.xlsm，目标工作簿为.xlsx).如果找到任何匹配项；在目标工作簿中，在匹配值的同一行上的"E"列中的值已更改.在这种情况下，必须将工作簿分开.

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.

我决定通过选择当前工作簿(A2)中的第一个值，将其值应用于变量，然后扫描目标工作簿中的"A"列以找到匹配项来进行匹配(总应至少有一个比赛).然后，将那些匹配的行的目标工作簿中"E"列的值更改为"DSC".然后，将当前工作簿中的所选单元格向下移动一个，并循环执行此过程，直到到达空白单元格为止.

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.

这是当前的代码:

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.

推荐答案

这是一个简化的工作示例.

Here is a simplified working example.

您需要将 Range("A1:A20")更改为要比较的范围.

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

您还可以将 Sheet("Sheet1")更改为适当的工作表.

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

AWorkbook 是 .xlsm 工作簿.

MasterWorkbook 是 .xlsx 工作簿.

如您所见，没有必要使用select.

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

您可以使用 For 或 For Each 循环遍历单元格.

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

通过嵌套for循环，您可以比较单元格，尽管对于较大的数据集，我可能会使用 Find 和 FindNext 而不是遍历单元格.

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.

如果值匹配，我将 DSC 放在目标工作簿中(列 E )

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

最后，关闭工作簿( 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

这篇关于根据当前工作簿中的匹配值编辑主工作簿中的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！