VBA: How do I reference a cell twice for a range?

[Peter h]

I'm trying to setup a QC chart that has a "Mean" line. I can do this simply when making the chart manually by entering (as example) $A$1,$A$1 into the "Y Values" field for the series. Well I'm trying to write code that will update the charts series ranges based on selections made from Data Validation cells on the sheet. So the ranges will change depending on the selections. I know how to use .Cells inside the .Range, but when I do that, it creates a range of cells, rather than 2 individual cell references. How can I reference 2 separate cells? Here's what I've got so far:

Sub TestRNG()
Dim tRNG            As Range
Dim tString         As String
Set tRNG = Worksheets("QC Limits").Range(Worksheets("QC Limits").Cells(1, 1) _
        , Worksheets("QC Limits").Cells(1, 1) _
        & "," & Worksheets("QC Limits").Cells(1, 1) _
        , Worksheets("QC Limits").Cells(1, 1))
        
tString = Worksheets("QC Limits").Cells(1, 1).Address _
        & "," & Worksheets("QC Limits").Cells(1, 1).Address
MsgBox tString
End Sub

I can set it up as a string easy enough, but that doesn't help me when I'm trying to set the range for the Y Values. tRNG breaks.

 

[Col Delane]

I'm not familiar with QC charts so can't help there, but suggest that the problem could be in initialising (populating) the tRNG variable with something that doesn't seem to resemble a range in Excel.

As your range object reference Worksheets("QC Limits").Cells(1, 1) is referring to single cell (being cell A1), I suggest you give that range a Defined Named in Excel (e.g. Name "MytRange" with RefersTo = "QC Limits"!A1 ),

and then initialise your VBA variable tRNG with this within your code, viz:

Set tRNG = Range("MytRange")

then

tString = tRNG.Address & "," & tRNG.Address

 

[pgc01]

Hi Peter

You can get the value from the cell, like:

Set tRNG = Worksheets("QC Limits").Range("A1").Value

and then enter is in an array in the .Yvalues of the series, something like:

    With cht.SeriesCollection(1)
        .Values = Array(tRNG.Value, tRNG.Value)
    End With

 

[pgc01]

Another way, better because the chart will update when you change the value in the cell, is to use your string, like:

Set tRNG = Worksheets("QC Limits").Range("A1")

and then

    With cht.SeriesCollection(1)
        .Values = tRNG.Address(External:=True) & "," & tRNG.Address(External:=True)
    End With

Which is what Col suggested. I just added the complete address.

 

[Peter h]

Thanks for the suggestion guys. I actually decided to set up strings rather than ranges so set my X and Y values for the series collections. It was much easier defining my exact ranges with strings then to try to figure out how to reference the correct cells in ranges. Here's examples of what I ended up going with:

xVals(0) = "=('" & uclRNG.Parent.Name & "'!" _
                & Worksheets(uclRNG.Parent.Name).Cells(1, 1).Address & ",'" _
                & uclRNG.Parent.Name & "'!" _
                & Worksheets(uclRNG.Parent.Name).Cells(64, 1).Address & ")"
                
xVals(1) = "='" & uclRNG.Parent.Name & "'!" _
                & Worksheets(uclRNG.Parent.Name).Cells(1, 1).Address _
            & ":" & Worksheets(uclRNG.Parent.Name).Cells(64, 1).Address
                
'UCL
'
yVals(0) = "=('" & uclRNG.Parent.Name & "'!" _
            & Worksheets(uclRNG.Parent.Name).Cells(uclRNG.Row + (7 * (mNum - 1)), uclRNG.Column + chNum).Address & ",'" _
            & uclRNG.Parent.Name & "'!" _
            & Worksheets(uclRNG.Parent.Name).Cells(uclRNG.Row + (7 * (mNum - 1)), uclRNG.Column + chNum).Address & ")"
            
'LCL
'
yVals(1) = "=('" & lclRNG.Parent.Name & "'!" _
            & Worksheets(lclRNG.Parent.Name).Cells(lclRNG.Row + (7 * (mNum - 1)), lclRNG.Column + chNum).Address & ",'" _
            & lclRNG.Parent.Name & "'!" _
            & Worksheets(lclRNG.Parent.Name).Cells(lclRNG.Row + (7 * (mNum - 1)), lclRNG.Column + chNum).Address & ")"