编写绑定按钮的VBA宏：双匹配条件下指定单元格输入1

Got it, let's walk through this step by step—you want a button that triggers a macro to drop a 1 into a specific cell on another sheet, but only after two matching checks pass. Here's how to make it work smoothly:

Step 1: Add a Button to Your Worksheet #

• First, make sure the Developer tab is visible (if not, enable it via File > Options > Customize Ribbon and check the box for Developer).
• Click Insert > Pick the Button (Form Control) option (not ActiveX—form controls are simpler for this use case).
• Draw the button on your current worksheet. When the "Assign Macro" window pops up, select "New" to open the VBA editor.

Step 2: Write the VBA Macro with Dual Matching Logic #

Paste this code into the module that opened. I've added comments to break down exactly what each part does:

Sub InsertOneOnMatch()
    Dim currentSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim b8Value As Variant
    Dim c4Value As Variant
    Dim matchRowPosition As Long
    Dim matchColPosition As Long
    Dim finalCell As Range
    
    ' Replace these sheet names with your actual sheet names!
    Set currentSheet = ThisWorkbook.Worksheets("YourCurrentSheet")
    Set targetSheet = ThisWorkbook.Worksheets("YourTargetSheet")
    
    ' Grab the values we need to match
    b8Value = currentSheet.Range("B8").Value
    c4Value = currentSheet.Range("C4").Value
    
    ' Match 1: Find the row in targetSheet's G11:G110 that matches B8 (like VLOOKUP's row lookup)
    On Error Resume Next ' Skip error if no match exists
    matchRowPosition = Application.Match(b8Value, targetSheet.Range("G11:G110"), 0)
    On Error GoTo 0 ' Reset error handling
    
    ' Match 2: Find the column in targetSheet's L4:FR4 that matches C4
    On Error Resume Next
    matchColPosition = Application.Match(c4Value, targetSheet.Range("L4:FR4"), 0)
    On Error GoTo 0
    
    ' Only proceed if both matches are found
    If matchRowPosition > 0 And matchColPosition > 0 Then
        ' Calculate the actual cell: start at G11's row + matched row offset, L4's column + matched column offset
        Set finalCell = targetSheet.Cells( _
            targetSheet.Range("G11").Row + matchRowPosition - 1, _
            targetSheet.Range("L4").Column + matchColPosition - 1 _
        )
        ' Drop the number 1 into the cell
        finalCell.Value = 1
    Else
        ' Optional: Alert if either match fails (you can remove this if you don't need it)
        MsgBox "Couldn't find one or both matches! Double-check B8 and C4 values.", vbExclamation
    End If
End Sub

Quick Logic Breakdown: #

• Worksheet References: Don't forget to replace "YourCurrentSheet" and "YourTargetSheet" with the real names of your sheets (typos here will break everything!).
• Row Match: Uses Application.Match to find where B8's value lives in G11:G110—this is exactly the row-finding logic VLOOKUP uses, but returns the relative position in the range. We add the starting row of G11 to get the actual sheet row number.
• Column Match: Does the same for C4's value in L4:FR4, then calculates the actual sheet column number using L4's starting column.
• Error Safety: The On Error lines prevent the macro from crashing if a match isn't found, and we only update the cell if both matches are valid.

Step 3: Link the Button to the Macro (If You Skipped It Earlier) #

• Right-click the button > Select Assign Macro
• Choose InsertOneOnMatch from the list > Click OK

Now when you click the button, it'll run the checks: if B8 matches a value in G11:G110 AND C4 matches a value in L4:FR4, it'll put a 1 in the intersecting cell on your target sheet. If either match fails, it'll pop up a heads-up.

内容的提问来源于stack exchange，提问作者KyleGReportWriter