Wrong number of arguments or invalid property assignment in VBA

Question

I'm getting the error message of

Wrong number of arguments or invalid property assignment

at this line:

Set hello = Range("O" & row, "Q" & row, "W" & row)

However, the macro runs smoothly with Set hello = Range("O" & row). So it means this macro is okay with one cell selection, but getting error with multiple cells selection. I cannot figure out which setting in my macro causes this error. Appreciate your help.

Sub ImportDatafromotherworksheet()
Dim wkbCrntWorkBook As Workbook
Dim wkbSourceBook As Workbook
Dim rngSourceRange As Range
Dim rngDestination As Range
Dim row As Integer
Dim hello As Range
Set wkbCrntWorkBook = ActiveWorkbook
With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count > 0 Then
        Workbooks.Open .SelectedItems(1)
        Set wkbSourceBook = ActiveWorkbook
        Set rngSourceRange = Application.InputBox(Prompt:="Select source row", Title:="Source Range", Default:="press Ctrl for multiple selection", Type:=8)
        row = rngSourceRange.row
        Set hello = Range("O" & row, "Q" & row, "W" & row)

        wkbCrntWorkBook.Activate
        Set rngDestination = Application.InputBox(Prompt:="Select destination cell", Title:="Select Destination", Default:="A1", Type:=8)
        hello.Copy rngDestination
        rngDestination.CurrentRegion.EntireColumn.AutoFit
        wkbSourceBook.Close False
    End If
End With
End Sub

Tim Williams · Accepted Answer · 2015-05-18 07:08:13Z

2

What you're trying to end up with should be (e.g.) Range("O5,Q5,W5")

Set hello = Range("O" & row & ",Q" & row & ",W" & row)

the whole thing is one string, not three.

answered May 18, 2015 at 7:08

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

Comments

Peter · Accepted Answer · 2015-05-18 07:13:25Z

2

Using .Union also works.

Set hello = Application.Union(Range("O" & row), Range("Q" & row), Range("W" & row))

Don't know if it's better than any other solution though.

answered May 18, 2015 at 7:13

Peter

931 silver badge10 bronze badges

Collectives™ on Stack Overflow

Wrong number of arguments or invalid property assignment in VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related