Range wrong number of arguments or invalid property assignment

Question

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

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

Which line does it break on? Click debug when the error comes up and see which line it is paused on in the vba editor — Tom, Oct 28 '16 at 14:09
It's a problem with these two calls: `Range(Cells(), Cells(), Cells()...)` The `Range` method can only take a maximum of two arguments (a starting cell and an ending cell) — tigeravatar, Oct 28 '16 at 14:09
It sound like you'll want to use `Union` instead of `Range` to get around that — tigeravatar, Oct 28 '16 at 14:09
@Tom When debuging it shows: Sub CopyHighLow() not the range line. — ArnoldasM, Oct 28 '16 at 14:10
Had just noticed what tigeravatar is saying. You want to be using 'Union' not 'Range'. 'Range' will only take two values, a start point and an end point (I think those points could be ranges too but might be wrong on that). Have a look at 'union' and it should work — Tom, Oct 28 '16 at 14:11
As a good answer is already given, you might also want to check out these links that might help you understand your fault better: [Range](https://msdn.microsoft.com/en-us/library/office/ff838238.aspx), [Union](https://msdn.microsoft.com/en-us/library/office/ff834621.aspx) — Fabian F, Oct 28 '16 at 14:19
@ArnoldasM It is a compile error rather than a run-time error. Even though `Sub CopyHighLow()` will be yellow-highlighted in the VBA editor, the word `Range` in the offending line should still be indicated (it looks grayed in mine). If you try 'Debug/Compile' in the editor it is easier to see where the error actually is (although you might need to make a minor change to be able to recompile). — John Coleman, Oct 28 '16 at 14:19
Just a note: `.Select` slows down the code and can be avoided 99% of the time. See this: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros . — Scott Craner, Oct 28 '16 at 14:19

Sgdva · Accepted Answer · 2016-10-28T14:25:57.800

Problem Explanation
Your problem relies in this line

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

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

More info at its official documentation

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.

score 2 · Answer 2 · answered Oct 28 '16 at 14:15

Corrected code using Union:

Sub CopyHighLow()

Dim i, j, produced, ordered

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
            Union(Cells(i, 1), Cells(i, 2), Cells(i, 3), Cells(i, 4), Cells(i, 20)).Select
            Selection.Copy Destination:=Sheets("Rytinis").Cells(j, 1)
            j = j + 1
        End If

i = i + 1
Wend
End Sub

score 0 · Answer 3 · answered Oct 28 '16 at 14:17

You need to tell it what sheet it copies from.

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
            ActiveSheet.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

Range wrong number of arguments or invalid property assignment

3 Answers3