CODEWITHSUNDEEP
excelvba
ArnoldasM
ArnoldasM

Reputation: 206

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

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

Upvotes: 0

Views: 4568

Answers (3)

Sgdva
Sgdva

Reputation: 2800

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.

enter image description here


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.

Upvotes: 3

Liss
Liss

Reputation: 441

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

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26640

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

Upvotes: 2

Related Questions