Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / May 2006

Tip: Looking for answers? Try searching our database.

Reworded Repost - Programatically Selecting a Range

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Debra Farnham - 30 May 2006 21:30 GMT
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
Don Guillett - 30 May 2006 22:31 GMT
Have you tried
data>filter>autofilter>filter on your date

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Hi again all
>
[quoted text clipped - 19 lines]
>
> Debra
Debra Farnham - 30 May 2006 22:42 GMT
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

> Have you tried
> data>filter>autofilter>filter on your date
[quoted text clipped - 22 lines]
>>
>> Debra
Don Guillett - 30 May 2006 22:48 GMT
Wasn't my post but autofilter may be better.

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Hi Don
>
[quoted text clipped - 56 lines]
>>>
>>> Debra
Debra Farnham - 30 May 2006 23:05 GMT
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

> Wasn't my post but autofilter may be better.
>
[quoted text clipped - 58 lines]
>>>>
>>>> Debra
Don Guillett - 30 May 2006 23:24 GMT
Sounds like the kind of project that many of us do for a living.

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> 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
[quoted text clipped - 70 lines]
>>>>>
>>>>> Debra
Debra Farnham - 31 May 2006 02:04 GMT
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

> Sounds like the kind of project that many of us do for a living.
>
[quoted text clipped - 73 lines]
>>>>>>
>>>>>> Debra
Debra Dalgleish - 31 May 2006 04:32 GMT
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
'===========================

> That may well be the case Don, but rest assured, I will not sleep tonite
> until I have resolved it :)
[quoted text clipped - 82 lines]
>>>>>>>
>>>>>>>Debra

Signature

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

Debra Farnham - 31 May 2006 20:22 GMT
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.

> 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
[quoted text clipped - 116 lines]
>>>>>>>>
>>>>>>>>Debra
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.