Reworded Repost - Programatically Selecting a Range

  • Thread starter Thread starter Debra Farnham
  • Start date Start date
D

Debra Farnham

Hi again all

How do I programatically select the range of cells in Columns B through D by
date in the following scenario:

Column A Column B Column C Column D
Jan 1/06 00:30 5 6
Jan 1/06 01:00 4 9
Jan 1/06 01:30 2 5
(and so on for the entire day until 23:30)
Jan 2/06 00:30 6 4
Jan 2/06 0:100 7 3
(and so on for the entire day until 23:30)

I will ultimately chart the data that is selected but I am having trouble
writing the code to simply select the proper range of cells for charting.

Even if someone could tell me how to programatically select the data in the
Columns B through D where the date matches 1/1/06 in Columns A , I can
probably figure out the rest.
Thanks again!

Debra
 
Hi Don

I was just about to answer my own question (thanks to you actually) ... I
found the answer in one of your other posts and ended up using the
following:

Dim myrange As Object, cellz As Object
Dim myrange2 As Object
Dim myFinishedRange As Object
Dim rngConstants As Object

On Error Resume Next
Set rngConstants = ActiveSheet.Cells.SpecialCells(xlConstants)

For Each cellz In rngConstants
If cellz.Value = ActiveCell.Value Then
If n = 0 Then
Set myrange = cellz
n = 1
Else
Set myrange = Union(myrange, cellz)
End If
End If
Next cellz
myrange.Select

Which seems to work like a charm ... now I just have to modify it to include
the adjacent columns which I don't anticipate will be a problem.

Thank you tons though!

Debra
 
It probably would .. but how I do programatically filter for say 1/1/06,
create the chart, remove the filter, then filter for 1/2/06, create the
chart and then loop through the entire month in the same manner? - of course
this is not just for January, a chart will have to be created for each day
of the year each month. (I sure hope this makes some sense).

Maybe I am approaching this all wrong?

I'm open to any suggestions.

Debra
 
That may well be the case Don, but rest assured, I will not sleep tonite
until I have resolved it :)

I'm the stubborn kind.

Thanks for your advice!

Debra
 
The following code may help you get started. It creates a chart for each
date, adjacent to the first record for the date. Assumes headings in row
1, and charts the data in columns C and D.

'================================
Sub CreateScatterChart()
Dim wsData As Worksheet
Dim rngDates As Range
Dim c As Range
Dim chObj As ChartObject
Dim lRow As Long
Set wsData = Sheets("Data")
lRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row
Set rngDates = wsData.Range(wsData.Cells(2, 1), wsData.Cells(lRow, 1))

For Each c In rngDates
If c.Value <> c.Offset(-1, 0).Value Then
Set chObj = ActiveSheet.ChartObjects.Add _
(Left:=c.Offset(0, 5).Left, Width:=375, _
Top:=c.Top, Height:=225)
With chObj.Chart
.ChartType = xlXYScatterLines
.SetSourceData Source:=c.Offset(0, 1).Resize(48, 3)
.HasTitle = True
.ChartTitle.Text = c.Value
.SeriesCollection(1).Name = wsData.Range("C1").Value
.SeriesCollection(2).Name = wsData.Range("D1").Value
End With
End If
Next c

End Sub
'===========================
 
Thank very kindly for your response Debra.

I didn't get a chance to check the groups until just now. I am going to
take what you have provided and play with it.

I managed to write code that loops through the days and creates the charts
but it isn't nearly as clean as your's.

Thanks again to both you and Don for your responses.

Debra F.
 
Back
Top