Defining a Range Address instead of cell contents

S

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
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.

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

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
I think all you need to do is to declare the variable as a Range.
Code: 
Dim RngEnd2 As Range
Set RngEnd2 = Cells.Find(What:=EndDate, After:=ActiveCell, LookIn:=xlFormulas, _        
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

Then you use it like

Code: 
MsgBox RngEnd2.Value & vbLf & RngEnd2.Address

After the set statement, RngEnd2 holds all the characteristic of the Workbook.Sheet.Range. ie. Workkbook(1).Sheets(1).Range("A1"). So you only need to use the variable and not the ancestry chain of the range.
 
Last edited:
Upvote 0
I am receiving an error at the msgbox saying an object is required. I'm going to post the broader script below. There are two problems resulting from this section of code one of which is this range object, the other is a consistent set of parameters for the charts.

Code: 
Dim aa As IntegerDim StartDate As String
Dim StartDate3 As Range
Dim EndDate As String
Dim EndDate3 As Range
Dim RngStart As Range
Dim RngEnd  As Range
Dim RngEnd2 As Range
Dim sh As Worksheet
Dim chrt As ChartObject
Dim ch As Chart
Dim zz As Integer
Dim NumObs2


NumObs2 = ActiveWorkbook.Sheets("AllDistanceMeasures").Range("C5", Range("C5").End(xlDown)).Rows.Count


For aa = 5 To NumObs2


    StartDate = ActiveWorkbook.Sheets("AllDistanceMeasures").Cells(aa, 9)
    EndDate = ActiveWorkbook.Sheets("AllDistanceMeasures").Cells(aa, 10)


    ActiveWorkbook.Sheets("ActiveSheet").Activate
    Range("A1").Select
    
        Set RngStart = Cells.Find(What:=StartDate, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        Set RngEnd = Cells.Find(What:=EndDate, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Offset(0, 1)
        
        Set RngEnd2 = Cells.Find(What:=EndDate, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        MsgBox RngEnd2.value & Rng2.Address
        
    'Set StartDate3 = ActiveWorkbook.Sheets("ActiveSheet").Range(RngStart)
    'Set EndDate3 = ActiveWorkbook.Sheets("ActiveSheet").Range(RngEnd).Offset(0, 1)
    Set sh = ActiveWorkbook.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
        '.SetSourceData Source:=Sheets("ActiveSheet").Range(StartDate3, EndDate3)
        .SeriesCollection.Add (ActiveWorkbook.Worksheets(DataSht).Range(DataRange))
        '.SeriesCollection.Add (ActiveWorkbook.Worksheets("ActiveSheet").Range(RngStart, RngEnd))
        .SeriesCollection(1).AxisGroup = 2
        .HasLegend = False
    End With
    
    For zz = 0 To NumObs - 1
    
        Sheets("ActiveSheet").Range(RngEnd2).Offset(zz, 0).Resize(, 2).Copy
        Sheets("LowDistCharts").Cells(5, aa * 2 + 5).Offset(zz, 0).PasteSpecial xlPasteValues
    
    Next zz
    
    
    'ActiveWorkbook.Sheets("ActiveSheet").Range(StartDate, EndDate).Resize(, 2).Copy
    'Sheets("LowDistCharts").Range("S3").End(xlToRight).Offset(0, 1).PasteSpecial xlPasteValues


Next aa
 
Upvote 0
The MsgBox was only to illustrate how to use the variables once they are set. But you need to use the full variable name or it produces errors like you got.
Code: 
MsgBox RngEnd2.value & [COLOR=#ff0000]Rng2.Address[/COLOR]

If you copy and paste code from the thread instead of retyping it, there will be less typos.
Need to Add .Value as shown here
Code: 
StartDate = ActiveWorkbook.Sheets(1).Cells(aa, 9).[COLOR=#daa520]Value
[/COLOR]EndDate = ActiveWorkbook.Sheets(1).Cells(aa, 10).[COLOR=#daa520]Value
[/COLOR]

Also, in the Find statements, I would use xlValues for the 'LookIn' parameter rather than xlFormulas, since the criteria is a value.
 
Last edited:
Upvote 0
Both the RngEnd and RngEnd2 are returning correct address values, but RngStart.Address is returning an object variable or with block variable error. I went ahead and made changes in the find statements and with the initial string variables startdate and enddate
 
Upvote 0
SBF12345 said:
Both the RngEnd and RngEnd2 are returning correct address values, but RngStart.Address is returning an object variable or with block variable error. I went ahead and made changes in the find statements and with the initial string variables startdate and enddate
Click to expand...

Your code needs a lot of work to make it run smoothly. If I get time, I will try to rewrite it and post back with some safeguards built in to prevent some of the alerts you get and to let you know when the values don't take hold.
 
Upvote 0
This will now stop your code if the variables cannot be set because a date cannot be found. I have assumed that you actually have a sheet named "ActiveSheet" although it was a bit confusing at first. O also made some changes to your variable for setting you loop parameters and noted the change in the code with comments. give it a try an post back if you still have a problem with it.

Code: 
Dim aa As Integer
Dim StartDate As String
Dim StartDate3 As Range
Dim EndDate As String
Dim EndDate3 As Range
Dim RngStart As Range
Dim RngEnd  As Range
Dim RngEnd2 As Range
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 'gets last row of range
'Changed from Rows.Count to Row so loop will go to end of column.
For aa = 5 To NumObs2
    StartDate = ActiveWorkbook.Sheets("AllDistanceMeasures").Cells(aa, 9).Value
    EndDate = ActiveWorkbook.Sheets("AllDistanceMeasures").Cells(aa, 10).Value
    
        Set RngStart = Sheet("ActiveSheet").Cells.Find(What:=StartDate, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
            If RngStart Is Nothing Then
                MsgBox "Variable did not set for 'RngStart'.  Check criteria value.", vbExclamation, "ALERT"
                Exit Sub
            End If
        Set RngEnd = Sheet("ActiveSheet").Cells.Find(What:=EndDate, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Offset(0, 1)
            If RngEnd Is Nothing Then
                MsgBox "Variable did not set for 'RngEnd'.  Check criteria value.", vbExclamation, "ALERT"
                Exit Sub
            End If
        Set RngEnd2 = Sheet("ActiveSheet").Cells.Find(What:=EndDate, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
            If RngEnd2 Is Nothing Then
                MsgBox "Varible did not set for 'RngEnd2'.  Check criteria value.", vbExclamation, "ALERT"
                Exit Sub
            End If
    'Set StartDate3 = ActiveWorkbook.Sheets("ActiveSheet").Range(RngStart)
    'Set EndDate3 = ActiveWorkbook.Sheets("ActiveSheet").Range(RngEnd).Offset(0, 1)
    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
            '.SetSourceData Source:=Sheets("ActiveSheet").Range(StartDate3, EndDate3)
            .SeriesCollection.Add (ActiveWorkbook.Worksheets(DataSht).Range(DataRange))
            '.SeriesCollection.Add (ActiveWorkbook.Worksheets("ActiveSheet").Range(RngStart, RngEnd))
            .SeriesCollection(1).AxisGroup = 2
            .HasLegend = False
        End With
        For zz = 0 To NumObs - 1
            Sheets("ActiveSheet").Range(RngEnd2).Offset(zz, 0).Resize(, 2).Copy
            Sheets("LowDistCharts").Cells(5, aa * 2 + 5).Offset(zz, 0).PasteSpecial xlPasteValues
        Next zz
    'ActiveWorkbook.Sheets("ActiveSheet").Range(StartDate, EndDate).Resize(, 2).Copy
    'Sheets("LowDistCharts").Range("S3").End(xlToRight).Offset(0, 1).PasteSpecial xlPasteValues
Next aa
 
Upvote 0
Use this instead, the other one would throuw an error instead of giving you the message.

Code: 
Dim aa As Integer
Dim StartDate As String
Dim StartDate3 As Range
Dim EndDate As String
Dim EndDate3 As Range
Dim RngStart As Range
Dim RngEnd  As Range
Dim RngEnd2 As Range
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 'gets last row of range
'Changed from Rows.Count to Row so loop will go to end of column.
For aa = 5 To NumObs2
    StartDate = Sheets("AllDistanceMeasures").Cells(aa, 9).Value
    EndDate = Sheets("AllDistanceMeasures").Cells(aa, 10).Value
        If StartDate <> "" Then
            Set RngStart = Sheet("ActiveSheet").Cells.Find(What:=StartDate, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        Else
            MsgBox "StartDate variable for " & Sheets("AllDistanceMeasures").Cells(aa, 9).Address & " not found", vbExclamation
            Exit Sub
        End If
        If EndDate <> "" Then            
            Set RngEnd = Sheet("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
        If EndDate<> "" Then            
            Set RngEnd2 = Sheet("ActiveSheet").Cells.Find(What:=EndDate, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        Else
            MsgBox "EndDate variable for " & Sheets("AllDistanceMeasures").Cells(aa, 10).Address & " not found", vbExclamation
            Exit Sub
       End If            
    'Set StartDate3 = ActiveWorkbook.Sheets("ActiveSheet").Range(RngStart)
    'Set EndDate3 = ActiveWorkbook.Sheets("ActiveSheet").Range(RngEnd).Offset(0, 1)
    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
            '.SetSourceData Source:=Sheets("ActiveSheet").Range(StartDate3, EndDate3)
            .SeriesCollection.Add (ActiveWorkbook.Worksheets(DataSht).Range(DataRange))
            '.SeriesCollection.Add (ActiveWorkbook.Worksheets("ActiveSheet").Range(RngStart, RngEnd))
            .SeriesCollection(1).AxisGroup = 2
            .HasLegend = False
        End With
        For zz = 0 To NumObs - 1
            Sheets("ActiveSheet").Range(RngEnd2).Offset(zz, 0).Resize(, 2).Copy
            Sheets("LowDistCharts").Cells(5, aa * 2 + 5).Offset(zz, 0).PasteSpecial xlPasteValues
        Next zz
    'ActiveWorkbook.Sheets("ActiveSheet").Range(StartDate, EndDate).Resize(, 2).Copy
    'Sheets("LowDistCharts").Range("S3").End(xlToRight).Offset(0, 1).PasteSpecial xlPasteValues
Next aa
 
Last edited:
Upvote 0
Both of the chunks of code work. The problem is in the acceptance of the variable values lower in the code when I attempt to use them as values in defining source data for the charts. I receive a "unable to set the values property of the series class" at the line:

Code: 
.SeriesCollection(2).Values = "=ActiveSheet!RngStart:RngEnd"

within the context of:

Code: 
With ch            .HasTitle = True
            .ChartTitle.Text = aa & " " & StartDate & " to " & EndDate
            .ChartTitle.Font.Size = 8
            .ChartType = xlLine
            .SetSourceData Source:=ActiveWorkbook.Worksheets(DataSht).Range(DataRange)
            .SeriesCollection.NewSeries
            .SeriesCollection(2).Values = "=ActiveSheet!RngStart:RngEnd"
            .SeriesCollection(1).Select
            .SeriesCollection(1).AxisGroup = 2
            .HasLegend = False
        End With

When I used the macro recorder to determine the structure of the charts properties I used as a basis from which to add another series. The Macro Recorder uses RC rather than A1. RngStart RngEnd are dimensioned as Ranges, but are showing values when I hover my mouse over them after they have been set. When I msgbox rngstart.address I get $A$1 values. I think I need to convert RngStart and RngEnd to a value type that will work with the charts property.

Code: 
 ActiveChart.SeriesCollection.NewSeries    ActiveChart.SeriesCollection(2).Values = "=ActiveSheet!R1636C6:R1640C6"
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).AxisGroup = 2
 
Upvote 0
If I add a line

Code: 
RngStartA = RngStart.Address

and RngStartA is a range variable I receive the error "object variable or with block variable not set"
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Locked
  • Question Question
Macros and protecting a cell
Replies
5
Views
76
Fluff
Fluff
L
  • Question Question
How to loop to find and replace with the specific cells
Replies
2
Views
297
Linda Lo
L
R
  • Question Question
cells.find error handling
2
Replies
13
Views
549
jolivanes
jolivanes
S
  • Solved
Search Function Macro not working
Replies
3
Views
62
MARK858
MARK858
B
  • Solved
Combine two routines
2 3
Replies
27
Views
439
NoSparks
N

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top