compile error wrong number of arguments or invalid property assignment [VBA]

Question

this should hopefully be an easy fix. So the code I have below runs fine when I only have 2 parameters from the first sheet being transferred to the second sheet, however when I try to add a third parameter (.Cells(c.row, "F"), my code receives the following error "compile error wrong number of arguments or invalid property assignment". Any thoughts as to why I can't seem to make my code work by adding more parameters when I need to be able to pull additional fields?

Sub copylist()
Dim wb As Workbook
Dim ws_employment As Worksheet
Dim c As Range
Dim i As Long

Set wb = ThisWorkbook
Set ws_employment = wb.Sheets("Employment")

'***This code fails due to adding ws_employment.cells(i, "F") & .cells(c.row, "F")***
'this will add values to the top table on the second sheet if there is an N value
i = 3 'this is the first row in the target sheet
With ws_employment_data
    For Each c In .Range("C3:C11").Cells
        If c.Value = "N" Then
            ws_employment.Range(ws_employment.Cells(i, "G"), ws_employment.Cells(i, "H"), ws_employment.Cells(i, "F")).Value = _
            .Range(.Cells(c.Row, "D"), .Cells(c.Row, "E"), .Cells(c.Row, "F")).Value
            i = i + 1
        End If
    Next c
'this will add values to the bottom table on the second sheet if there is a Y value
i = 12
    For Each c In .Range("C3:C11").Cells
        If c.Value = "Y" Then
            ws_employment.Range(ws_employment.Cells(i, "G"), ws_employment.Cells(i, "H")).Value = _
            .Range(.Cells(c.Row, "D"), .Cells(c.Row, "E")).Value
            i = i + 1
        End If
    Next c
End With



'***This code (is the same as above) works whilst having removed ws_employment.cells(i, "F") & .cells(c.row, "F")***
'this will add values to the top table on the second sheet if there is an N value
i = 3 'this is the first row in the target sheet
With ws_employment_data
    For Each c In .Range("C3:C11").Cells
        If c.Value = "N" Then
            ws_employment.Range(ws_employment.Cells(i, "G"), ws_employment.Cells(i, "H"), ws_employment.Cells(i, "F")).Value = _
            .Range(.Cells(c.Row, "D"), .Cells(c.Row, "E"), .Cells(c.Row, "F")).Value
            i = i + 1
        End If
    Next c
'this will add values to the bottom table on the second sheet if there is a Y value
i = 12
    For Each c In .Range("C3:C11").Cells
        If c.Value = "Y" Then
            ws_employment.Range(ws_employment.Cells(i, "G"), ws_employment.Cells(i, "H")).Value = _
            .Range(.Cells(c.Row, "D"), .Cells(c.Row, "E")).Value
            i = i + 1
        End If
    Next c
End With

MsgBox ("DONE")

End Sub

I think instead of ws_employment.Range(ws_employment.Cells(i, "G"), ws_employment.Cells(i, "H"), ws_employment.Cells(i, "F")).Value you want ws_employment.Range("F" & i & ":H" & i).Value? — Siddharth Rout
– Siddharth Rout, Commented Aug 15, 2021 at 19:40
.Range(.Cells(c.Row, "D"), .Cells(c.Row, "E"), .Cells(c.Row, "F")).Value needs to change too. D to F. — Gokhan Aycan
– Gokhan Aycan, Commented Aug 15, 2021 at 19:41
Hi @GokhanAycan, I'm confused by what you're trying to say, the values need to change to what exactly? — bgado
– bgado, Commented Aug 16, 2021 at 1:43

Raymond Wu · Accepted Answer · 2021-08-16 02:07:39Z

2

The Range property only accepts 2 parameters (documentation) so you can only specify the 1st cell and the last cell.

I have also removed wb variable as it doesn't seems necessary.

Try this code:

Sub copylist()

    Dim ws_employment As Worksheet
    Dim c As Range
    Dim i As Long

    Set ws_employment = ThisWorkbook.Sheets("Employment")

    'this will add values to the top table on the second sheet if there is an N value
    i = 3 'this is the first row in the target sheet
    With ws_employment_data
        For Each c In .Range("C3:C11").Cells
            If c.Value = "N" Then
                ws_employment.Range(Replace("F?:H?", "?", i)).Value = _
                .Range(Replace("D?:F?", "?", c.Row)).Value
                i = i + 1
            End If
        Next c
    'this will add values to the bottom table on the second sheet if there is a Y value
    i = 12
        For Each c In .Range("C3:C11").Cells
            If c.Value = "Y" Then
                ws_employment.Range(ws_employment.Cells(i, "G"), ws_employment.Cells(i, "H")).Value = _
                .Range(.Cells(c.Row, "D"), .Cells(c.Row, "E")).Value
                i = i + 1
            End If
        Next c
    End With
    
    MsgBox "DONE"

End Sub

answered Aug 16, 2021 at 2:07

Raymond Wu

3,4272 gold badges9 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

bgado Over a year ago

Hi @Raymond Wu, can you explain what is supposed to take the place of the (?). I assume once I fill in that blank, then that will get rid of the "Object required error" that I receive. Thanks!

Raymond Wu Over a year ago

@bgado The ? Are supposed to be replace with the row number e.g. if i = 3 then it will become F3:H3. Which line did you get the error?

bgado Over a year ago

I went ahead and filled in the ?'s with 3 or whatever row number necessary but still receive the Object Required Error. It begins during the (For Each c in .Range("C3:C11").Cells row

Raymond Wu Over a year ago

Do you have a worksheet with the codename ws_employment_data? If not then you need to change it @bgado

Collectives™ on Stack Overflow

compile error wrong number of arguments or invalid property assignment [VBA]

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related