Compile Error: Argument not optional

[DiogoCF]

Hi everyone,

I am not a pro programer and I am trying to plot a great amount of data and to place the resulting graphs organized at a new sheet. In order to do so, I am making a macro with a simpler data set (image attached). The final purpose is to skip some columns with variable column lengths. My idea was to produce a simple macro, even if it is not the most elegant way to do it. the code is:

Public Sub chats()


Dim xaxis As Range
Dim yaxis As Range
Dim A As Characters
Dim B As Characters
Dim i As Integer


    For i = 1 To 2
        Dim c As Chart
        Set c = ActiveWorkbook.charts.Add
        Set c = c.Location(Where:=Cells(1, i * 6), Name:="Sheet2")
        With c
            .ChartType = xlXYScatterLines
        End With
        A = MsgBox.Cells(1, 2 * i - 1).Adress(RowAbsolute:=False, ColumnAbsolute:=False)
        B = MsgBox.Cells(1, 2 * i).Adress(RowAbsolute:=False, ColumnAbsolute:=False)
        xaxis = Range(A, Range(A).End(xlDown))
        yaxis = Range(B, Range(B).End(xlDown))
        Dim s As Series
        Set s = c.SeriesCollection.NewSeries
        With s
            .Values = yaxis
            .XValues = xaxis
        End With
        Next i
End Sub

and, when I compile it says that there is a "Compile Error: Argument not optional" right at the beginning and I do not get why...
Thank you for your time and help.

 

[RoryA]

Welcome to the forum.

What is MsgBox meant to be in your code? It's a built in VBA function, which is why the compiler is complaining.

Also, you have misspelled Address

 

[DiogoCF]

Welcome to the forum.

What is MsgBox meant to be in your code? It's a built in VBA function, which is why the compiler is complaining.

Also, you have misspelled Address

Thank you for your reply!

I misspelled "Address" and I have corrected it but it did not solved the problem. I use the MsgBox to convert the coordinates "(1,1)" into "A1", because I see the Range function written everywhere with the "cell name" instead of coordinates. I googled how to do it and this was one of the solutions.

 

[RoryA]

I'm not sure where you found that, but that is not what MsgBox does - it just puts a message up on your screen. The Address property will return the address in A1 format by default:

        A = Cells(1, 2 * i - 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)
        B = Cells(1, 2 * i).Address(RowAbsolute:=False, ColumnAbsolute:=False)

 

[DiogoCF]

I'm not sure where you found that, but that is not what MsgBox does - it just puts a message up on your screen. The Address property will return the address in A1 format by default:

        A = Cells(1, 2 * i - 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)
        B = Cells(1, 2 * i).Address(RowAbsolute:=False, ColumnAbsolute:=False)

Thanks, you are right, of course. Now it is working but it is not doing what was supposed. I think I must change the whole code again, but thank you for your help!