438 error, when trying to set a variable to a cell on another sheet

[Boogerbut74]

Right now im trying to set the variable rng as cell H39 (H39 will be populated with a capital letter of a column) in my reference sheet and then have rng defing the range in the variable Graph Range.

Sub Test_7()
   
    Dim lastrow As Integer
    Dim GraphRange As Range
    Dim rng As Range
   
    Worksheets("Reference").rng = Range("H39")
   
   
   
    lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 10).End(xlUp).Row
   
    'set chart data range (including series names)
    Set GraphRange = ActiveSheet.Range("D10:" & rng & lastrow)
   
    'Create a chart
    Set cht = ActiveSheet.ChartObjects.Add( _
    Left:=ActiveCell.Left, _
    Width:=450, _
    Top:=ActiveCell.Top, _
    Height:=250)
   
    'Give chart some data
    cht.Chart.SetSourceData Source:=GraphRange

   
   
    'Determine the chart type
    cht.Chart.ChartType = xlXYScatterSmooth
    'edits chart detailes
    cht.Chart.Axes(xlCategory).MinimumScale = 0
    cht.Chart.Axes(xlCategory).MaximumScale = 100
   
    cht.Chart.Axes(xlValue).MinimumScale = 115
    cht.Chart.Axes(xlValue).MaximumScale = 140
   
    cht.Chart.Legend.Position = xlLegendPositionBottom
 
    cht.Select
ActiveChart.FullSeriesCollection(1).Delete
   
   
End Sub

Sub Test_6()
   
    Dim lastrow As Integer
    Dim GraphRange As Range
   
    lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 10).End(xlUp).Row
   
    'set chart data range (including series names)
    Set GraphRange = ActiveSheet.Range("D10:T" & lastrow)
   
    'Create a chart
    Set cht = ActiveSheet.ChartObjects.Add( _
    Left:=ActiveCell.Left, _
    Width:=450, _
    Top:=ActiveCell.Top, _
    Height:=250)
   
    'Give chart some data
    cht.Chart.SetSourceData Source:=GraphRange
   
    'Determine the chart type
    cht.Chart.ChartType = xlXYScatterSmooth
    'edits chart detailes
    cht.Chart.Axes(xlCategory).MinimumScale = 0
    cht.Chart.Axes(xlCategory).MaximumScale = 100
   
    cht.Chart.Axes(xlValue).MinimumScale = 115
    cht.Chart.Axes(xlValue).MaximumScale = 140
   
    cht.Chart.Legend.Position = xlLegendPositionBottom
End Sub

 

[rlv01]

Declare your variables.

Dim rng as Range

Remember to use the Set statement when assigning a range variable.

Worksheets("Reference").rng = Range("H39")

Set rng = Range("H39")

 

[Boogerbut74]

the range is only for a specific cell in another sheet when using your code it reference the active sheet

 

[rlv01]

...when using your code it reference the active sheet

Worksheets("Reference").rng = Range("H39")

The line above is your code. The right side (Worksheets("Reference").rng) is a gross syntax error. You cannot reference a range variable that way. The left side of the equation (Range("H39")) references cell H39 on the active sheet. Your code references the active sheet, so my response to you also referenced the active sheet. If you want something different, you have to explain what you mean. If cell H39 is intended to be on another sheet then you have to fully qualify the reference. Something like this:

Set rng = ActiveWorkbook.Worksheets("Reference").Range("H39")