如何用VBA结合Index Match跨工作表查找单元格并写入数据？

Hey there! As a fellow VBA learner, I know how frustrating those random errors can be when you're starting out. Let's walk through a working, commented code example that does exactly what you need, plus break down each part so you understand how it works.

The Full Code #

Sub UpdatePressSheet()
    ' Declare variables to store our worksheets, values, and target cell
    Dim wsInput As Worksheet
    Dim wsPress As Worksheet
    Dim empID As Variant
    Dim inputDate As Date
    Dim matchRow As Variant
    Dim matchCol As Variant
    Dim targetCell As Range
    
    ' Set references to our worksheets (avoids typos later!)
    Set wsInput = ThisWorkbook.Worksheets("Input and Info Page")
    Set wsPress = ThisWorkbook.Worksheets("Press")
    
    ' Get employee ID from B7 on Input sheet
    empID = wsInput.Range("B7").Value
    ' Check if employee ID is empty
    If empID = "" Then
        MsgBox "Please enter an employee ID in cell B7!", vbExclamation
        Exit Sub
    End If
    
    ' Get date from user input box (Type:=2 ensures only dates are accepted)
    On Error Resume Next ' Handle case where user cancels input
    inputDate = Application.InputBox( _
        Prompt:="Enter the date you want to update:", _
        Title:="Select Date", _
        Default:=Date, _
        Type:=2)
    On Error GoTo 0
    
    ' Check if user canceled or entered invalid date
    If inputDate = 0 Then
        MsgBox "Invalid date or input canceled. Exiting.", vbInformation
        Exit Sub
    End If
    
    ' Find the row for the employee ID in Press sheet (assuming IDs are in column A)
    matchRow = Application.Match(empID, wsPress.Range("A:A"), 0)
    ' Find the column for the date in Press sheet (assuming dates are in row 1)
    matchCol = Application.Match(inputDate, wsPress.Range("1:1"), 0)
    
    ' Check if either the employee ID or date wasn't found
    If IsError(matchRow) Then
        MsgBox "Employee ID " & empID & " not found in Press sheet!", vbCritical
        Exit Sub
    End If
    If IsError(matchCol) Then
        MsgBox "Date " & Format(inputDate, "mm/dd/yyyy") & " not found in Press sheet!", vbCritical
        Exit Sub
    End If
    
    ' Set the target cell to the intersection of the found row and column
    Set targetCell = wsPress.Cells(matchRow, matchCol)
    
    ' Write the data from B8 to the target cell
    targetCell.Value = wsInput.Range("B8").Value
    
    ' Let the user know it worked!
    MsgBox "Successfully updated cell " & targetCell.Address & " with data from B8.", vbInformation
End Sub

How This Works (Step-by-Step) #

• Variable Declarations: We start by defining variables to keep our code organized and avoid bugs from untyped values.
• Worksheet References: Using Set to point to our worksheets means we don't have to type the full name every time, and we catch typos early.
• Employee ID Check: We make sure B7 isn't empty before proceeding—no sense looking for a blank ID!
• Date Input: The Application.InputBox with Type:=2 forces the user to enter a valid date (or cancel). We handle the cancel case so the code doesn't crash.
• Finding the Target Cell:
  • Application.Match looks for the employee ID in column A of the Press sheet and returns the row number. Unlike WorksheetFunction.Match, it doesn't throw an error if no match is found—instead, it returns an error value we can check.
  • Same logic for the date in row 1 of the Press sheet to get the column number.
• Error Handling: We check if either the ID or date wasn't found, then show a friendly message instead of a confusing error.
• Writing the Data: Finally, we take the value from B8 and put it into the target cell, then confirm success to the user.

Quick Adjustments for Your Sheet Layout #

If your Press sheet isn't set up with IDs in column A and dates in row 1, just change these lines:

• For employee IDs in column C instead of A: matchRow = Application.Match(empID, wsPress.Range("C:C"), 0)
• For dates in row 3 instead of row 1: matchCol = Application.Match(inputDate, wsPress.Range("3:3"), 0)

Troubleshooting Common Errors #

• "Subscript out of range": This usually means the sheet name is misspelled. Double-check that "Input and Info Page" and "Press" match exactly (including spaces and capitalization).
• "Type mismatch": If you get this when getting the date, the user might have entered text instead of a date. The Type:=2 in the InputBox should prevent this, but make sure your system's date format matches what you're entering.
• No match found: Verify that the employee ID in B7 is exactly the same (case doesn't matter, but extra spaces do!) as in the Press sheet. Same for the date—make sure the date in the Press sheet is stored as a date value, not text.

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