Defining a Range Address instead of cell contents

[SBF12345]

I would like to define a range value like "A1" to the variable RngEnd2. EndDate is a string value . The purpose for setting RngEnd2 is to use it as a range value in a subsequent statement.

Set RngEnd2 = Cells.Find(What:=EndDate, After:=ActiveCell, LookIn:=xlFormulas, _        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

 

[JLGWhiz]

If I add a line

RngStartA = RngStart.Address

and RngStartA is a range variable I receive the error "object variable or with block variable not set"

.SeriesCollection(2).Values = "=ActiveSheet!" & Sheets("ActiveSheet").Range(RngStart, RngEnd).Address

 

[SBF12345]

Receiving the error "unable to set the values property of the worksheet function class". Is there a conversion method or something similar that I could use to assign a new variable to rngstart.address. For a seemingly trivial conflict this takes the cake!

 

[JLGWhiz]

Receiving the error "unable to set the values property of the worksheet function class". Is there a conversion method or something similar that I could use to assign a new variable to rngstart.address. For a seemingly trivial conflict this takes the cake!

Should have caught this earlier. the statement is using plural Values instead of singular value to begin with, but I don't believe you even want to use Value in that statement to get the collection to use the range as a data series. try this.

.SeriesCollection(2) = "=ActiveSheet!" & Sheets("ActiveSheet").Range(RngStart, RngEnd).Address

 

[SBF12345]

Below is what I have now and it seems to be running OK with some minor hangups. The problem I was running into ended up being solved by redefining the range back to a string hence the line:

RngStartR = RngStart.Address

I am now having trouble with the charts in two areas that I am aware of. One, during the first iteration the chart is including source data from an additional source not obviously defined in the "with ch statement", and two other sources simply defined as "={1}". I am not sure how any of these are making it to the chart on the first iteration only. The other problem is arising from the two y-axis. Both axis include data values describing the values of the related series, it is just that one of the series is different in terms of the visibility of the volatility of the time series data. For instance it looks relatively flat compared to the gyrations of the other series. I would like to view the two series on roughly the same variability scale. Side note, I do have plans to swap out a piece of the code for a Log array, which should help with viewing in this capacity. Making progress...

Dim aa As IntegerDim StartDate As String
Dim EndDate As String
Dim RngStart As Range
Dim RngEnd  As Range
Dim RngStartR As String
Dim RngStartRng As Range
Dim RngEndR As String
Dim RngEndRng As Range
Dim RngXR As String:            RngXR = ActiveWorkbook.Sheets("ActiveSheet").Range("C7").value
Dim RngXR2 As String:           RngXR2 = ActiveWorkbook.Sheets("ActiveSheet").Range("C8").value
Dim sh As Worksheet
Dim chrt As ChartObject
Dim ch As Chart
Dim zz As Integer
Dim NumObs2 As Long


NumObs2 = Sheets("AllDistanceMeasures").Cells(Rows.Count, 3).End(xlUp).Row


For aa = 5 To NumObs2


    StartDate = Sheets("AllDistanceMeasures").Cells(aa, 9).value
    EndDate = Sheets("AllDistanceMeasures").Cells(aa, 10).value
    
        If StartDate <> "" Then
        
            Set RngStart = Sheets("ActiveSheet").Cells.Find(What:=StartDate, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Offset(0, 1)
            
        Else
            MsgBox "StartDate variable for " & Sheets("AllDistanceMeasures").Cells(aa, 9).Address & " not found", vbExclamation
            Exit Sub
            
        End If
        
        RngStartR = RngStart.Address
        
        If EndDate <> "" Then
        
            Set RngEnd = Sheets("ActiveSheet").Cells.Find(What:=EndDate, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Offset(0, 1)
            
        Else
            MsgBox "EndDate variable for " & Sheets("AllDistanceMeasures").Cells(aa, 10).Address & " not found", vbExclamation
            Exit Sub
            
        End If
        
        RngEndR = RngEnd.Address
       
    Set sh = Worksheets("LowDistCharts")
    Set chrt = sh.ChartObjects.Add(0, 0, 300, 300)
    Set ch = chrt.Chart
    
        With chrt
            .Height = 300
            .Width = 300
            .Top = 1 + ((aa - 4) * 300)
            .Left = 1
        End With
        
        With ch
            .HasTitle = True
            .ChartTitle.Text = aa & " " & StartDate & " to " & EndDate
            .ChartTitle.Font.Size = 8
            .ChartType = xlLine
            .SeriesCollection.NewSeries
            .SeriesCollection(1).Values = ActiveWorkbook.Worksheets("ActiveSheet").Range(RngXR, RngXR2)
            '.SeriesCollection(1).AxisGroup = 1
            .SeriesCollection.NewSeries
            .SeriesCollection(2).Values = ActiveWorkbook.Worksheets("ActiveSheet").Range(RngStartR, RngEndR)
            .SeriesCollection(2).AxisGroup = 2
            .HasLegend = False
        End With
        
        For zz = 0 To NumObs - 1
        
            Sheets("ActiveSheet").Range(RngEndR).Offset(zz, 0).Copy
            Sheets("LowDistCharts").Cells(5, aa + 5).Offset(zz, 0).PasteSpecial xlPasteValues
            
        Next zz

 

[JLGWhiz]

Well, I guess I was wrong about the Values property. I have not worked with collections that much and am not thoroughly familiar with all the syntax. If you have your original problem solved, I suggest you now start a new thread for your chart problem.

 

[SBF12345]

Will do, Thanks for all your help and commitment during this thread. very much appreciated!

 

[JLGWhiz]

Will do, Thanks for all your help and commitment during this thread. very much appreciated!

You're welcome,
Regards, JLG