VBA代码在一工作表可用，另一表报1004及对象要求错误

Hey there! Let's work through those two errors you're hitting with your requisition number lookup code. I'll break down what's causing each issue and show you a revised version of the code that should work smoothly.

First Error: "Unable to get the Activate property of the Range class" #

The .Cells(1, 1).Activate line is throwing this error because you can't activate a cell unless its parent worksheet is active. When you're using a With block for a range on a non-active sheet, calling .Activate directly on the range will fail.

Worse yet, you don't even need to activate a cell to use the Find method! The after parameter can point directly to a cell in your range, no activation required. This makes your code more reliable (since it doesn't depend on which sheet is active when it runs).

Second Error: "Object required" at Set cF = .Find #

This is likely caused by one of two issues:

• Typos in the worksheet name: Your code uses " MthruF Schedule" (note the leading space). Double-check that your actual worksheet name matches this exactly—if the sheet doesn't have that leading space, the With block will reference a non-existent range, and Find will fail to return a valid object.
• No matching requisition number: If Find doesn't locate your input value, it returns Nothing, but your existing code does check for this later. However, if the worksheet reference is invalid, the error will trigger before that check.

Revised Code #

Here's a fixed version of your code with explanations of key changes:

Sub FindAndFillReqNumber()
    Dim ReqNumber As Variant
    ReqNumber = InputBox("Please Enter Requisition Number", "Information")
    
    ' Exit if user cancels or enters nothing
    If ReqNumber = "" Then Exit Sub
    
    Dim FirstAddress As String, cF As Range
    Dim targetSheet As Worksheet
    
    ' Set the target worksheet explicitly (check for existence first)
    On Error Resume Next
    Set targetSheet = ThisWorkbook.Sheets("MthruF Schedule") ' Removed leading space—adjust if your sheet actually has it!
    On Error GoTo 0
    
    ' If worksheet doesn't exist, show error and exit
    If targetSheet Is Nothing Then
        MsgBox "Worksheet 'MthruF Schedule' not found!", vbExclamation
        Exit Sub
    End If
    
    With targetSheet.Range("A:A")
        ' Use the last cell in column A as the "after" parameter so Find starts at A1
        Set cF = .Find(What:=ReqNumber, _
                     After:=.Cells(.Cells.Count), _
                     LookIn:=xlFormulas, _
                     LookAt:=xlPart, _
                     SearchOrder:=xlByColumns, _
                     SearchDirection:=xlNext, _
                     MatchCase:=False, _
                     SearchFormat:=False)
        
        If Not cF Is Nothing Then
            FirstAddress = cF.Address
            Do
                ' Fill in the offset cells with your data
                cF.Offset(0, 8).Value = ReqNumber
                cF.Offset(0, 9).Value = EmpInfo.FirstName.Value & " " & EmpInfo.LastName.Value
                cF.Offset(0, 10).Value = EmpInfo.ComboBox3.Value & "/" & EmpInfo.ComboBox20.Value & "/" & EmpInfo.YearCmb.Value
                cF.Offset(0, 11).Value = EmpInfo.AgencyBox.Value
                
                Set cF = .FindNext(cF)
            Loop While Not cF Is Nothing And cF.Address <> FirstAddress
        Else
            ' Optional: Notify user if no match was found
            MsgBox "Requisition number '" & ReqNumber & "' not found!", vbInformation
        End If
    End With
End Sub

Key Changes: #

1. Removed the .Activate line: We replaced the after:=ActiveCell with after:=.Cells(.Cells.Count)—this tells Find to start searching from the top of column A (since it wraps around after the last cell).
2. Explicit worksheet reference: We added a check to make sure the target worksheet exists, which prevents errors if the sheet name is misspelled.
3. Input validation: We exit the sub if the user cancels the InputBox or enters an empty value.
4. Optional no-match notification: Added a message box to let the user know when their requisition number isn't found (you can remove this if you don't need it).

Double-check that the worksheet name in Set targetSheet = ... matches your actual sheet name exactly (including spaces or punctuation). That's the most common culprit for the "object required" error here!

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