范围错误的参数数量或无效的属性分配 [英] Range wrong number of arguments or invalid property assignment

问题描述

我正在尝试将选定的单元格复制到另一个工作表，但是我总是收到错误消息:参数数量错误或属性分配无效

I'm trying to copy selected cells to another sheet, but I'm always getting error message: Wrong number of arguments or invalid property assignment

此代码检查"Cells(i，20)"是否小于或大于"Cells(i，4)" 10％.如果不是，它将删除该行；如果是，则应将选定的单元格复制到从第48行开始的另一个工作表中.

This code checks if "Cells(i, 20)" is less or greater than "Cells (i, 4)" by 10%. If it's not, it deletes the row, if it is it should copy selected cells to another sheet starting 48 row.

也许有人可以指出，我在这里做错了什么?这是我的代码的样子:

Maybe someone could point out, what I'm doing wrong here? Here's how my code looks like:

Sub CopyHighLow()
Sheets("ProductionHighLow").Select
i = 2
j = 48
produced = 0
While Cells(i, 1) <> "" Or Cells(i + 1, 1) <> ""
  produced = Cells(i, 20)
  ordered = Cells(i, 4)
  If Cells(i, 20) > Cells(i, 4) * 0.9 And Cells(i, 20) < Cells(i, 4) * 1.1 Then
    Cells(i, 22).Delete Shift:=xlUp
    i = i - 1
  Else
    Range(Cells(i, 1), Cells(i, 2), Cells(i, 3), Cells(i, 4), Cells(i, 20)).Select
    Selection.Copy Destination:=Sheets("Rytinis").Range(Cells(j, 1), Cells(j, 2), Cells(j, 3), Cells(j, 4), Cells(j, 5))
    j = j + 1
  End If
  i = i + 1
Wend
End Sub

这里的更新是有效的修改版本:

UPDATE here is working modified version:

Sub CopyHighLow()
Sheets("ProductionHighLow").Select
i = 2
j = 48
produced = 0
While Cells(i, 1) <> "" Or Cells(i + 1, 1) <> ""
        produced = Cells(i, 20)
        ordered = Cells(i, 4)
        If Cells(i, 20) > Cells(i, 4) * 0.9 And Cells(i, 20) < Cells(i, 4) * 1.1 Then
             Cells(i, 22).Delete Shift:=xlUp
             i = i - 1
        Else
           Set RangeUnionCopy = Union(Cells(i, 1), Cells(i, 2), Cells(i, 3), Cells(i, 4), Cells(i, 20))
           Set RangeUnionPaste = Union(Cells(j, 1), Cells(j, 2), Cells(j, 3), Cells(j, 4), Cells(j, 5))
           RangeUnionCopy.Copy Destination:=Sheets("Rytinis").Range(RangeUnionPaste.Address)
            j = j + 1
        End If

i = i + 1
Wend
End Sub

推荐答案

问题说明
您的问题取决于这一行

Problem Explanation
Your problem relies in this line

Range(Cells(j, 1), Cells(j, 2), Cells(j, 3), Cells(j, 4), Cells(j, 5))

Range对象不能处理2个以上的命名单元格(通过这种方式).您可能会直接在编译器中看到它.

The Range object cannot handle more than 2 named cells (this way). You may see it directly in the compiler.

更多信息，请参见官方文档

解决方案:
我会在此之前使用Union，就像这样:

Approach solution:
I would use Union prior to this, like so:

Set RangeUnion = Union(Cells(i, 1), Cells(i, 2), Cells(i, 3), Cells(i, 4), Cells(i, 20))
RangeUnion.Copy Destination:=Sheets("Rytinis").Range(RangeUnion.Address)

这应该可以满足您的目标.

This should work for what you are aiming for.

这篇关于范围错误的参数数量或无效的属性分配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！