VBA-运行此代码时出现运行时错误"1004"

问题描述

我是VBA的新手，我正在尝试将两个不同的列合并到另一个工作表中的一个列中.

I am new to VBA and I am trying to merge two different columns into one column in another worksheet.

我的问题是出现运行时错误'1004'应用程序或对象定义的错误".

My problem is that I get a "runtime error '1004' application- or object-defined error".

从我阅读的内容来看，该错误很可能与 Sheets("Tabelle1")有关，但我不知道如何解决.

From what I have read the error most likely has something to do with Sheets("Tabelle1")but I do not know how to fix it.

这是代码:

Sub test2()
    Dim name1 As Range, size As Integer
    size = WorksheetFunction.CountA(Columns(3))

    With Sheets("Tabelle1")
        For Each name1 In Sheets("advprsrv").Range("D2:D" & size)
            If Not (Trim(name1.Value & vbNullString) = vbNullString) Then
                .Cells(name1.Row, 1).Value = LCase(name1.Value & " " & Range(Cells(name1.Row, name1.Column)))
            End If
        Next name1
    End With
End Sub

我用 F8 遍历了代码，看来错误出现在此行 ws.Cells(name1.Row，1).Value = LCase(name1.).值&"和范围(单元格(name1.Row，name1.Column).Value))

I went through the Code with F8 and it seems that the error pops up at this line ws.Cells(name1.Row, 1).Value = LCase(name1.Value & " " & Range(Cells(name1.Row, name1.Column).Value))

推荐答案

尝试以下代码，并在代码注释中进行解释:

Try the code below, explanations inside the code comments:

Option Explicit

Sub test2()

    Dim name1 As Range, size As Long
    Dim TblSht As Worksheet
    Dim advpSht As Worksheet

    ' set the worksheet object and trap errors in case it doesn't exist
    On Error Resume Next
    Set TblSht = ThisWorkbook.Worksheets("Tabelle1")
    On Error GoTo 0
    If TblSht Is Nothing Then ' in case someone renamed the "Expense" Sheet
        MsgBox "Tabelle1 sheet has been renamed", vbCritical
        Exit Sub
    End If

    ' set the worksheet object and trap errors in case it doesn't exist
    On Error Resume Next
    Set advpSht = ThisWorkbook.Worksheets("advprsrv")
    On Error GoTo 0
    If advpSht Is Nothing Then ' in case someone renamed the "Expense" Sheet
        MsgBox "advprsrv sheet has been renamed", vbCritical
        Exit Sub
    End If

    With advpSht
        ' safer way to get the last row from column "D" (since later on you use it in a Range in Column "D")
        size = .Cells(.Rows.Count, "D").End(xlUp).Row

        For Each name1 In .Range("D2:D" & size)
            If Trim(name1.Value2) <> "" Then
                ' ***** Not sure about the connection between the 2 sheets *****
                TblSht.Cells(name1.Row, 1).Value = LCase(name1.Value2 & " " & TblSht.Cells(name1.Row, name1.Column))
            End If
        Next name1
    End With

End Sub

这篇关于VBA-运行此代码时出现运行时错误"1004"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！