运行VBA宏出现Run-time error 1004错误，请求代码问题排查

Hey there, let's break down what's causing that Run-time Error 1004 and fix it step by step—since you're new to VBA, I'll keep things clear and avoid jargon where I can.

错误根源 #

The error hits this line because of a common VBA pitfall:

NumRows = Worksheets("Data").Range("A5", Range("A5").End(xlDown)).Rows.Count

The problem is the second Range("A5").End(xlDown) part—it doesn't specify which worksheet it belongs to. VBA defaults to using the active worksheet at runtime. If your active sheet isn't "Data" when the macro runs, this creates a mismatched range reference (combining cells from two different sheets) and triggers the 1004 error.

修正步骤 & 优化代码 #

Here's how to fix this, plus some tweaks to make your macro more reliable (especially for new VBA users):

1. Fix the NumRows calculation

Always explicitly reference the worksheet for all range calls. You can use a With block to clean this up and avoid repeating Worksheets("Data") over and over:

With Worksheets("Data")
    NumRows = .Range("A5", .Range("A5").End(xlDown)).Rows.Count
End With

Notice the dot . before Range—that ties it to the worksheet specified in the With statement.

2. Ditch unnecessary Select calls

Using Select and ActiveCell is slow and prone to errors (it depends on the user not clicking away while the macro runs). You already have the x loop variable to target cells, so we can remove all Select/ActiveCell lines entirely.

3. Use Long instead of Integer for row counts

Excel worksheets can have way more rows than the maximum value of an Integer (32767). Switching to Long prevents overflow errors if your dataset grows.

4. Fix the wdFormatPlainText issue (hidden gotcha!)

In your Emails sub, wdFormatPlainText is a Word constant. If you haven't referenced the Microsoft Word Object Library in your VBA project, this will throw another error. For simplicity, replace it with its numeric value 2 (which is what Word uses behind the scenes).

完整修正后的代码 #

Updated loopCheck Sub

Public Sub loopCheck()
    Dim NumRows As Long ' Changed from Integer to Long
    Dim eID As String
    Dim eName As String
    Dim eEmail As String
    Dim supportGroup As String
    Dim managerEmail As String
    Dim acName As String
    Dim x As Long
    Dim wsData As Worksheet ' Add a worksheet variable for clarity
    
    Application.ScreenUpdating = False
    Set wsData = Worksheets("Data") ' Assign the worksheet to a variable
    
    ' Calculate number of rows correctly
    With wsData
        NumRows = .Range("A5", .Range("A5").End(xlDown)).Rows.Count
    End With
    
    For x = 1 To NumRows
        ' Fetch values directly without selecting
        eID = wsData.Range("A" & x + 4).Value
        eName = wsData.Range("B" & x + 4).Value
        eEmail = wsData.Range("C" & x + 4).Value
        supportGroup = wsData.Range("F" & x + 4).Value
        managerEmail = wsData.Range("G" & x + 4).Value
        acName = wsData.Range("I" & x + 4).Value
        
        ' Prepare local table
        wsData.Range("AA5").Value = eID
        wsData.Range("AB5").Value = eName
        wsData.Range("AC5").Value = eEmail
        wsData.Range("AF5").Value = supportGroup
        managerEmail = managerEmail & ";" & wsData.Range("AA1").Value ' Use & instead of + for string concatenation
        
        ' Call Emails function
        Call Emails(acName, eEmail, managerEmail)
    Next x
    
    Application.ScreenUpdating = True
    Set wsData = Nothing ' Clean up the variable
End Sub

Updated Emails Sub

Public Sub Emails(x As String, y As String, z As String)
    Dim outlook As Object
    Dim newEmail As Object
    Dim xInspect As Object
    Dim pageEditor As Object
    Dim a As String
    Dim b As String
    Dim c As String
    Dim wsMF As Worksheet
    Dim wsData As Worksheet
    
    a = y
    b = z
    c = x
    Set wsMF = Worksheets("MF")
    Set wsData = Worksheets("Data")
    
    Set outlook = CreateObject("Outlook.Application")
    Set newEmail = outlook.CreateItem(0)
    
    With newEmail
        .To = a
        .CC = b
        .BCC = ""
        .Subject = wsMF.Range("A1") & c
        .Body = ""
        .Display
        
        Set xInspect = newEmail.GetInspector
        Set pageEditor = xInspect.WordEditor
        
        ' Replace wdFormatPlainText with 2 to avoid library reference issues
        wsMF.Range("A9").Copy
        pageEditor.Application.Selection.PasteAndFormat 2
        
        wsMF.Range("A3").Copy
        pageEditor.Application.Selection.PasteAndFormat 2
        
        wsData.Range("AA4:AF5").Copy
        pageEditor.Application.Selection.PasteAndFormat 2
        
        wsMF.Range("A5").Copy
        pageEditor.Application.Selection.PasteAndFormat 2
        
        wsMF.Range("A7").Copy
        pageEditor.Application.Selection.PasteAndFormat 2
        
        .Send
    End With
    
    ' Clean up all objects
    Set pageEditor = Nothing
    Set xInspect = Nothing
    Set newEmail = Nothing
    Set outlook = Nothing
    Set wsMF = Nothing
    Set wsData = Nothing
End Sub

Quick Notes for New VBA Users #

• Always qualify your ranges with a worksheet (either via With or a worksheet variable) to avoid mismatched references.
• Avoid Select/Activate whenever possible—they're almost never necessary and cause more problems than they solve.
• Use & instead of + for string concatenation in VBA ( + can cause unexpected behavior if one of the values isn't a string).

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