Transmettre adresse cellule entre fonctions

[Benzema]

Bonjour,

J'aimerai savoir comment transmettre 1 variable contenant l'adresse d'une cellule a une autre fonction. (sans avoir la reference de la feuille de calcul, pour la reutiliser la meme adresse de cellule sur 1 autre feuille ) Je sais qu'il faut declarer en globale au dessus des fonctions, mais apres je me heurte au fait que les cellules enregistrees egalement le nom de la feuille en plus de l'adresse.

Je vous joins mon code avec les variables en rouge que j'aimerai pouvoir transmettre d'une feuille a une autre par les biais des 2 fonctions.

Merci d'Avance,
Clem

Option Explicit



Dim c As ChartObject
Dim calc As Range, y As Range, y1 As Range, x As Range, numreport As Integer, s As Series

Dim y_choice As Range, y1_choice As Range, x_choice As Range, v As Range

Sub tests()!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
' tests Macro
' Macro recorded 14.10.2010 by Amilien 19.10.2010






'Calculate the number of values in a column
Set calc = Range("A:A").Find("not implemented", , xlValues, xlWhole, , , False)


'Y-axis selection---------------------------------------------------------------------------------

If Not calc Is Nothing Then
Set y_choice = Application.InputBox("Choose the Y axis column (address title's cell)", Type:=8)
'Calculate the number of values in a column
Set y = Range(y_choice.Offset(1, 0), Cells(calc.Row - 1, y_choice.Column))
y.Name = "y": y_choice.Name = "y_choice"

End If

If Not calc Is Nothing Then
Set y1_choice = Application.InputBox("Choose the Y1 axis column (address title's cell)", Type:=8)

Set y1 = Range(y1_choice.Offset(1, 0), Cells(calc.Row - 1, y1_choice.Column))

y1.Name = "yy": y1_choice.Name = "y1_choice"
End If


'X-axis selection----------------------------------------------------------------------------------

If Not calc Is Nothing Then
Set x_choice = Application.InputBox("Choose the X axis column (address title's cell)", Type:=8)
Set x = Range(x_choice.Offset(1, 0), Cells(calc.Row - 1, x_choice.Column))
x.Select

x.Name = "x": x_choice.Name = "x_choice"

End If







' Chart configurations ---------------------------------------------------------------------------------

With Sheets("Sheet1")
Set c = .ChartObjects.Add(.Range("G15").Left, .Range("G15").Top, 800, 400)
End With

With c.Chart
.ChartType = xlLineMarkers
.SetSourceData Source:=ActiveSheet.Range("y,yy"), PlotBy:=xlColumns
.SeriesCollection(1).XValues = "=TX_WLAN_11n_framed_802.11n_HT40!x"
.SeriesCollection(2).XValues = "=TX_WLAN_11n_framed_802.11n_HT40!yy"

.SeriesCollection(2).AxisGroup = 2
.HasTitle = True
.ChartTitle.Characters.Text = "XY Graph"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = Range("x_choice")
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = Range("y_choice")
.Legend.Position = xlBottom
.Legend.Font.Size = 8
.Legend.Font.Bold = True
.ChartTitle.Text = "XY Graph"
.ChartTitle.Font.Size = 8
.ChartTitle.Font.Bold = True
.Axes(xlCategory).TickLabels.Font.Size = 8
.Axes(xlValue).TickLabels.Font.Size = 8
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = Range("y1_choice")
.SeriesCollection(1).Name = Range("y_choice")
.SeriesCollection(2).Name = Range("y1_choice")
End With
End Sub



Sub rajoutSerie()!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Dim c As ChartObject, s As Series, y As Range



Set c = Sheet2.ChartObjects(1)
With c.Chart
Set s = .SeriesCollection.NewSeries
With s
.Values = Sheet4.Range("y") ERREUR ICI
.Name = Sheet2.Range("A1").Text
End With
End With
End Sub

 

[tototiti2008]

Re : Transmettre adresse cellule entre fonctions

Bonjour Benzema,

si la feuille ne t'intéresse pas, n'utilise pas d'objets Range mais des variables String

Dim y As String
...
[COLOR=#ff0000]y = Range(y_choice.Offset(1, 0), Cells(calc.Row - 1, y_choice.Column)).[B]Address[/B][/COLOR]
...
[COLOR=#ff0000][COLOR=#000000]Values = Sheet4.Range([/COLOR][COLOR=red]y[/COLOR][COLOR=#000000]) [/COLOR][/COLOR]