Trying to run this program, but getting error " compile error, wrong number of arguments, or invalid property assignment"

Question

Private Sub CommandButton1_Click()

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LastRow

        If Cells(i, 1) = "Wheat" Then
            Range(Cells(i, 2), Cells(i, 3), Cells(i, 4)).Select
            Selection.Copy


            Workbooks.Open Filename:="C:\commodities\allcommodities-new.xlsm"
            Worksheets("Sheet2").Select

            erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


            ActiveSheet.Cells(erow, 51).Select
            ActiveSheet.Paste
            ActiveWorkbook.Save
            ActiveWorkbook.Close

        End If

    Next i

        For i = 2 To LastRow

            If Cells(i, 1) = "Feeder Cattle" Then
            Range(Cells(i, 2), Cells(i, 3), Cells(i, 4)).Select
            Selection.Copy


            Workbooks.Open Filename:="C:\commodities\allcommodities-new.xlsm"
            Worksheets("Sheet2").Select

            erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


            ActiveSheet.Cells(erow, 3).Select
            ActiveSheet.Paste
            ActiveWorkbook.Save
            ActiveWorkbook.Close

        End If


    Next i

        For i = 2 To LastRow

            If Cells(i, 1) = "Corn" Then
            Range(Cells(i, 2), Cells(i, 3), Cells(i, 4)).Select
            Selection.Copy


            Workbooks.Open Filename:="C:\commodities\allcommodities-new.xlsm"
            Worksheets("Sheet2").Select

            erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

            ActiveSheet.Cells(erow, 67).Select
            ActiveSheet.Paste
            ActiveWorkbook.Save
            ActiveWorkbook.Close
        End If

    Next i
end sub

NOTE: The code fails at the first "Range" command with a "compile error, wrong number of arguments, or invalid property assignment" I can get the code to run with 2 cells definitions in the Range command.

Aren't you going to be opening, saving and closing the external file multiple times if you find wheat multiple times? — , Jun 10 '17 at 14:50

score 2 · Accepted Answer · 2017-06-10T15:02:55.457

While you can state range("B1, C1, D1") you cannot state range("B1", "C1", "D1") which is what you are trying to do.

If you actually want columns 2, 3 and 4 on row i then just use the first and the last like range("B1:D1")

Range(Cells(i, 2), Cells(i, 4)).Select

If the actual columns are a discontiguous group then use Union.

dim rng as range
set rng = union(Cells(i, 2), Cells(i, 4), Cells(i, 6))
rng.select

Please look into How to avoid using Select in Excel VBA macros.

Option Explicit

Private Sub CommandButton1_Click()
    Dim i As Long, lastRow As Long, nextRow As Long
    Dim wbACN As Workbook

    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Set wbACN = Workbooks.Open(Filename:="C:\commodities\allcommodities-new.xlsm")

    For i = 2 To lastRow
        Select Case LCase(Cells(i, 1).Value2)
            Case "wheat"
                Union(Cells(i, 2), Cells(i, 3), Cells(i, 4)).Copy _
                  Destination:=wbACN.Worksheets("Sheet2").Cells(Rows.Count, "AY").End(xlUp).Offset(1, 0)
            Case "feeder cattle"
                Union(Cells(i, 2), Cells(i, 3), Cells(i, 4)).Copy _
                  Destination:=wbACN.Worksheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
            Case "corn"
                Union(Cells(i, 2), Cells(i, 3), Cells(i, 4)).Copy _
                  Destination:=wbACN.Worksheets("Sheet2").Cells(Rows.Count, "BO").End(xlUp).Offset(1, 0)
            Case Else
                'do notbhing
        End Select
    Next i

    wbACN.Close savechanges:=True

End Sub

related: [Is the . in .Range necessary when defined by .Cells?](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells). — , Jun 10 '17 at 15:14

Trying to run this program, but getting error " compile error, wrong number of arguments, or invalid property assignment"

1 Answers1