Re: Reworded Repost - Programatically Selecting a Range

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.

"Debra Dalgleish" <dsd@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:447D0E64.2030200@xxxxxxxxxxxxxxxxxxxxxxx
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 Work***
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 = Active***.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
'===========================

Debra Farnham wrote:
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

"Don Guillett" <dguillett1@xxxxxxxxxxxxx> wrote in message
news:Ojh6CfDhGHA.764@xxxxxxxxxxxxxxxxxxxxxxx

Sounds like the kind of project that many of us do for a living.

--
Don Guillett
SalesAid Software
dguillett1@xxxxxxxxxxxxx
"Debra Farnham" <nospam@xxxxxxxxxx> wrote in message
news:u9DRxUDhGHA.4404@xxxxxxxxxxxxxxxxxxxxxxx

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

"Don Guillett" <dguillett1@xxxxxxxxxxxxx> wrote in message
news:eT1cULDhGHA.3996@xxxxxxxxxxxxxxxxxxxxxxx

Wasn't my post but autofilter may be better.

--
Don Guillett
SalesAid Software
dguillett1@xxxxxxxxxxxxx
"Debra Farnham" <nospam@xxxxxxxxxx> wrote in message
news:uURyAIDhGHA.1324@xxxxxxxxxxxxxxxxxxxxxxx

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 = Active***.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

"Don Guillett" <dguillett1@xxxxxxxxxxxxx> wrote in message
news:u3WveBDhGHA.4144@xxxxxxxxxxxxxxxxxxxxxxx

Have you tried
data>filter>autofilter>filter on your date

--
Don Guillett
SalesAid Software
dguillett1@xxxxxxxxxxxxx
"Debra Farnham" <nospam@xxxxxxxxxx> wrote in message
news:%23Ut8ofChGHA.3900@xxxxxxxxxxxxxxxxxxxxxxx

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













--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html



.

Loading