.PasteSpecial不适用于Range对象

Y helo thar,

actually my vba-knowledge is quite good and normally I don't have many difficulties coding , but this is driving me nuts.

实际上我的vba知识非常好,通常我没有很多编码困难,但这让我疯了。

Code is pretty easy. I have a worksheet PR_DB where all my projects are stored. For every project there are a number of employees, saved in cells (sel_pr, >10+) (employee name and ID).

代码非常简单。我有一个工作表PR_DB,其中存储了我的所有项目。对于每个项目,都有许多员工,保存在单元格中(sel_pr,> 10+)(员工姓名和ID)。

I want to delete an employee from the project and tidy up the project database entry. All employees are listed in two listboxes. The ones that are working in the project, and the ones that don't. Via the buttons I can add and remove employees from either listbox.

我想从项目中删除一名员工并整理项目数据库条目。所有员工都列在两个列表框中。在项目中工作的那些,以及那些没有的。通过按钮,我可以从列表框中添加和删除员工。

When I add an employee to a project (top button), my Sub just puts the ID&Name at the end of the row of said project. When I remove them from a project and therefore from the database, I look for the cell with the employee data. Afterwards I just want to cut all the cells to the right and paste them one cell to the left (via offset) so the deleted name is overwritten.

当我将员工添加到项目(顶部按钮)时,我的Sub只将ID和Name放在所述项目行的末尾。当我从项目中删除它们并因此从数据库中删除它时,我会查找包含员工数据的单元格。之后我只想将所有单元格切割到右边并将它们粘贴到左边的一个单元格(通过偏移),这样就删除了删除的名称。

This is my code:

这是我的代码:

Sub delMA_from_prBetList()

    Dim i, j, k, listRow, lastRowMA_DB, lastRowPR_DB, sel_pr As Integer
    Dim wsPR, wsMA_DB, wsPR_DB As Worksheet
    Dim foundMA As Boolean
    Dim cutRng, pasteRng As Range

    Set wsPR = Worksheets("Projekte")
    Set wsMA_DB = Worksheets("MA_DB")
    Set wsPR_DB = Worksheets("PR_DB")
    lastRowPR_DB = wsPR_DB.UsedRange.Rows.Count

    'check if any employee was selected
    If IsNull(wsPR.prBetListe.Value) = True Then
        MsgBox "Please select an employee."
        Exit Sub
    End If
    j = 10


    'look for selected project in DB 
    For i = 2 To lastRowPR_DB
        If wsPR_DB.Cells(i, 1) = CInt(wsPR.prListe.Value) Then
            'row ID of said project
            sel_pr = i
        End If
    Next

    'find employee

    Do Until wsPR_DB.Cells(sel_pr, j) = ""        
        'employees are saved as "ID;NAME"

        If wsPR_DB.Cells(sel_pr, j) = wsPR.prBetListe.Value & ";" & wsPR.prBetListe.Column(1, wsPR.prBetListe.ListIndex) Then
            'when found, look for last cell with an entry
            k = j
            Do Until wsPR_DB.Cells(sel_pr, k) = ""
                k = k + 1
            Loop

               'set cutRng so it spans from cell right to the found employee
               'to last cell with an employee in that project
                Set cutRng = wsPR_DB.Range(wsPR_DB.Cells(sel_pr, j + 1), wsPR_DB.Cells(sel_pr, k))
                'set pasteRng like cutRng, just one cell further to the left
                'so the deleted employee will be overwritten
                Set pasteRng = cutRng.Offset(rowOffset:=0, columnOffset:=-1)
                cutRng.Cut
                pasteRng.PasteSpecial


                Exit Do
        End If

        j = j + 1
    Loop

're-initialize listboxes     
Call init_maListe_dyn
Call init_prBetListe_dyn

End Sub

So whats the problem? Everything works just fine, all the cells I want to cut go into clipboard, but they arent pasted into the pasteRng. Error is Error 1004 "Application-defined or Object-defined error". I tried a thousand things but the solution is probably too easy to find.

所以有什么问题?一切正常,我想剪切的所有单元格都会进入剪贴板,但它们并没有粘贴到pasteRng中。错误是错误1004“应用程序定义或对象定义的错误”。我尝试了一千件事,但解决方案可能太容易找到了。

Hope you can help me, thanks in advance.

希望你能提前帮助我。

PS: I'm kind of in a hurry, so that text might not be as well formatted as it could be. Please bear with me.

PS:我有点匆忙,因此文本的格式可能不尽如人意。请多多包涵。

2 个解决方案

#1

just use directly:

直接使用:

cutRng.Cut pasteRng

this should solve your problem ;)

这应该可以解决你的问题;)

#2

If you want to offset one column to the left it should be

如果要将一列向左偏移,则应该是

Set pasteRng = cutRng.Offset(,-1)

Also, you're not defining how you want to paste. The paste settings may not be set to paste values if they were set differently prior to this instance (via code or otherwise). If you want to just cut-paste the values it would be

此外,您没有定义要粘贴的方式。如果在此实例之前以不同方式设置粘贴设置(通过代码或其他方式),则粘贴设置可能不会设置为粘贴值。如果你想只是剪切粘贴它的值

cutRng.Cut
pasteRng.PasteSpecial xlPasteValues

Let me know if that helps at all and if not I'll look further into it.

让我知道这是否有帮助,如果没有,我会进一步研究它。

#1