VBA cell selection "application.union" with cells()

Question

I'm having trouble selecting parts of two different lines in a sub. My sub must create a chart with these lines a DataSource, and the number of the second line is an argument of the procedure. CompteLigneDebut and CompteColonnefin are functions that I defined earlier, they return an integer. Here's the part that causes the issue, the specific line that causes an error is set x... :

Sub test(ligne As Integer)
    Dim graphe As Chart
    Dim x As Range
    Set graphe = Charts.Add
    graphe.ChartType = xlColumnClustered
    Set x = Application.Union(Range(Cells(ligne, 2), Cells(ligne, CompteColonneFin)), Range(Cells(CompteLigneDebut, 2), Cells(CompteLigneDebut, CompteColonneFin - 1)))
    graphe.SetSourceData (x)

Thank you for your help/advice

Answer 1

The Union looks ok and the code below works fine for me. I think one of your functions is the problem.

Sub RunTheTest()

    test 3

End Sub
Sub test(ligne As Integer)
    Dim x As Range

    Set x = Application.Union(Range(Cells(ligne, 2), Cells(ligne, CompteColonneFin)), Range(Cells(CompteLigneDebut, 2), Cells(CompteLigneDebut, CompteColonneFin - 1)))

End Sub

Private Function CompteColonneFin() As Integer
    CompteColonneFin = 2
End Function

Private Function CompteLigneDebut() As Integer
    CompteLigneDebut = 2
End Function

Try splitting the Application.Union line into smaller bits. Then check what values the functions return.

Sub test(ligne As Integer)
    Dim x As Range
    Dim colonneFin As Integer
    Dim ligneDebut As Integer

    colonneFin = CompteColonneFin()
    ligneDebut = CompteLigneDebut()

    Set x = Range(Cells(ligne, 2), Cells(ligne, colonneFin))
    Set x = Application.Union(x, Range(Cells(ligneDebut, 2), Cells(ligneDebut, colonneFin - 1)))

End Sub