Excel VBA - First line throwing "Argument not optional"

Question

I'm trying to develop a sub that pulls data from the same cell one worksheet at a time and copies it to a worksheet called "Summary" and pastes it in to create a table of values. However, I am getting the error "Argument not optional" and the first line is highlighted as the issue (the one with Sub SummaryAssemble()), yet there doesn't seem to be a reason for that since it isn't necessary to pass anything into the subroutine.

Sub SummaryAssemble()

    Dim i As Integer

    Dim x As Integer

    x = Sheets.Count


    For i = 3 To x Step 1

    Sheets(i).Activate
        ' Copy and paste the value into sheet where A
        ' Range.Select($A
        ' First copy value for ATRT 2014 (cell B4)
       Range.Select ("B4")
       Selection.Copy
       Sheets(Summary).Activate
       ' Select Column B Row i (2, i)
       Range(2, i).Select
       ActiveCell.Paste

       Sheets(i).Activate

        Sheets(ActiveSheet.Index + 1).Activate
    Next i
End Sub

Do you have another function or sub that is called SummaryAssemble? — Scott Craner, Apr 27 '16 at 20:13

score 6 · Accepted Answer · edited May 23 '17 at 12:07

6

The problem is actually in this line,

Range.Select ("B4")

It should be closer to,

Range("B4").Select

Couple of other problems

Sub SummaryAssemble()
    Dim i As Long, x As Long

    x = Sheets.Count

    For i = 3 To x Step 1
          ' Copy and paste the value into sheet where A
          ' First copy value for ATRT 2014 (cell B4)
        Sheets(i).Range("B4").Copy _
                Destination:=Sheets("Summary").Cells(2, i)
        'or as a direct value transfer (PasteSpecial Paste:=xlvalues)
        'Sheets("Summary").Cells(2, i) = Sheets(i).Range("B4").Value
    Next i
End Sub

I believe your use of Summary should have been quoted as a literal string and there is no need to select something for a copy and paste operation. Finally, you were mixing and mismatching the syntax for a Range object and a Range.Cells property; e.g. it is Range("B4") and Cells(2, i).

_{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

edited May 23 '17 at 12:07

Community

1
1

answered Apr 27 '16 at 20:16

It looks like the line Range("B4")... is throwing an error as well. It says "Application Defined or Object Defined error" – camelCaseCowboy Apr 27 '16 at 20:31
I missed the bit with `Range(2, i)` . It should have been `Cells(2, i)`. – Apr 27 '16 at 20:41
Is there a way to do the same but copying as values (since it's a "Desination" command there's no paste values option)? I will need it for copying the value of a formula over. – camelCaseCowboy Apr 27 '16 at 21:01
1

I've added a direct value transfer method above. Remember that the [Range.Cells property](https://msdn.microsoft.com/en-us/library/office/ff196273.aspx) is `Cells(, )` which is sort of backwards from `Range("B4")` (you are incrementing the column not the row). It is possible that you meant `Cells(i, 2)` not `Cells(2, i)`. – Apr 27 '16 at 21:06

vacip · Answer 2 · 2016-04-27T20:41:03.383

Let me add one thing to Jeeped's answer. Whenever you see the Sub getting highlighted after an error, it is usually not the culprit. The yellow highlight doesn't actually mean "There is a problem with this row." It merely means "This is the next row of code to be executed." Or in other words, "This is where I have stopped executing the program."

In cases like this, look for the selected or highlighted code (Range in your case). That is the one causing the problem - a Compile error.

Before a Sub could be executed, it needs to be compiled. When this happens, it is checked for errors. Some specific types of errors won't even let the code be compiled, so it won't even begin execution.

There are three major types of errors. A syntax error is when the code doesn't make sense. It can't be compiled, because it violates the basic rules of the language. Like saying "Grumblutty wadahargs."

Then there is the Runtime error. This happens when the words do make sense, maybe even the whole sentence is OK grammatically, but in the current context it tries something that can't be done. Like saying "Put the lorry on your head and go for a run." Not gonna work.

The third type of error is the favourite of all programmers: The Logic error. Syntax is OK, the code runs, no error messages, but something goes horribly wrong. Like: "Open the window and step out." This technically can be done, but it will hurt. Programmers spend hours and hours hunting this type of error. A lot of fun...

Excel VBA - First line throwing "Argument not optional"

2 Answers2