VBA - 根據標準匹配來自另一個工作表的數據 (VBA - Match Data from another Worksheet based on Criteria)

問題描述

VBA ‑ 根據標準匹配來自另一個工作表的數據 (VBA ‑ Match Data from another Worksheet based on Criteria)

我有兩個工作簿，我們稱它們為 Workbook1 和 Workbook2

Workbook1 ：看起來像這樣:(I只將數據放在 A 列）

Facility Name| Column B| Column C |
   TGG751‑M  |         |          |
   TGG760‑M  |         |          |
   TLY701‑M  |         |          |

Workbook 2:

Technology|  Facility Name |        Remarks        |
 LTE      |   TGG751‑M     |  91%‑100% utilization |
 LTE      |   TGG760‑M     |  91%‑100% utilization |
 LTE      |   TLY701‑M     |         Red           |

我需要在兩個工作簿中匹配 Facility Name 和如果匹配的Facility Name的Remarks為91%‑100%利用率在中添加兩列 WorkBook2 並將其標記為 Selected 和 For Checking。

輸出是這樣的..

 Technology   | Facility Name |        Remarks        | +1 Column | +1 Column
     LTE      |   TGG751‑M    |  91%‑100% utilization | Selected  | For Checking
     LTE      |   TGG760‑M    |  91%‑100% utilization | Selected  | For Checking
     LTE      |   TLY701‑M    |         Red           |           | 

我的代碼：

Public Sub Selection()

Set WorkBook1 = Workbooks.Open(TextBox2.Text).Sheets(1)
Set WorkBook2 = Workbooks.Open(TextBox5.Text).Sheets(1)

        lngLastRow = WorkBook1 .Range("A" & WorkBook1 .Rows.Count).End(xlUp).Row

For Index = 2 To lngLastRow   

    Dim varFacility As Variant
    Dim facilityRng As Range

    On Error Resume Next

        lngLastRow = WorkBook1 .Range("A" & WorkBook1 .Rows.Count).End(xlUp).Row
        Set facilityRng = WorkBook1 .Range("A1:A" & lngLastRow )

        varFacility = WorkBook1 .Range("A" & rownum).Value
        varPosition = Application.WorksheetFunction.Match(varFacility, facilityRng, 0)

    If Err = 0 Then

        WorkBook1 .Range("A" & rownum).Value = WorkBook2 .Range("B" & varPosition).Value

        If WorkBook2 .Range("C" & rownum).Value Like "91%‑100% utilization*" Then

           WorkBook2 .Range("D" & rownum).Value = Selected
           WorkBook2 .Range("E" & rownum).Value = For Checking

    End If
   End If

End Sub

如何做到這一點？一旦我運行我的代碼，它就無法工作，但我沒有任何錯誤。

參考解法

方法 1:

You cannot open workbooks and assign workbook‑type vars to worksheets like that. It also seemed to me that your logic in locating facility names that existed in both workbooks/worksheets was backwards. This opens both workbooks, assigns worksheet‑type vars to the first worksheet in each workbook and loops through the entries in the second workbook using the facility there to confirm existence in workbook 1.

Public Sub Selection()
    Dim lastRow As Long, rw As Long, rngFacility As Range, strRemark As String
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet

    'TextBox2 and TextBox5 need to be recognized here
    Set wb1 = Workbooks.Open(TextBox2.Text, ReadOnly:=True)
    Set ws1 = wb1.Worksheets(1)
    Set wb2 = Workbooks.Open(TextBox5.Text)
    Set ws2 = wb2.Worksheets(1)

    lastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row
    Set rngFacility = ws1.Range("A1:A" & lastRow)
    strRemark = "91%‑100% utilization"

    With ws2
        lastRow = .Range("B" & Rows.Count).End(xlUp).Row
        For rw = 2 To lastRow
            If .Range("C" & rw) = strRemark Then
                If Not IsError(Application.Match(.Range("B" & rw), rngFacility, 0)) Then
                    .Range("D" & rw).Resize(1, 2) = _
                        Array("Selected", "For Checking")
                End If
            End If
        Next rw
    End With

    'wb1.Close savechanges:=False
    'wb2.Close savechanges:=True

End Sub

Given the nature of the user‑form text boxes and double workbooks, I did not test this from start to finish but it does compile and represents the process I would use to go about this.

方法 2:

I just solved this problem using the code i provided.

Public Selection()

       Set Sheet2 = Workbooks.Open(TextBox2.Text).Sheets(1)
       Set Sheet5 = Workbooks.Open(TextBox5.Text).Sheets(1)

        Dim varFacility As Variant
        Dim facilityRng As Range

            lngLastRow = Sheet5.Range("B" & Sheet5.Rows.Count).End(xlUp).Row
            Set facilityRng = Sheet5.Range("B1:B" & lngLastRow)

        For rownum = 2 To Sheet2.Range("A" & Sheet2.Rows.Count).End(xlUp).Row
            varFacility = Sheet2.Range("A" & rownum).Value

        On Error Resume Next
            varPosition = Application.WorksheetFunction.Match(varFacility, facilityRng, 0)

            If Err = 0 Then

                If Sheet5.Range("C" & varPosition).Value Like "91%‑100% utilization*" Then
                    Sheet2.Range("C" & rownum).Value = "Selected"
                    Sheet2.Range("D" & rownum).Value = "For Checking"
                End If
            End If
        On Error GoTo 0

        Next

 End Sub

(by 7A65726F、user4039065、7A65726F)

參考文件

1. VBA ‑ Match Data from another Worksheet based on Criteria (CC BY‑SA 2.5/3.0/4.0)